1

There are 3 tables: events, eventClients and clients.

Write a query that finds events that all clients have access to?

Inner join doesn't guarantee that all rows in a table participate so this doesn't help:

select * from events e
inner join eventclients ec on e.id = ec.eventid
inner join clients c on etc.clientid = c.id
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
The Light
  • 26,341
  • 62
  • 176
  • 258

3 Answers3

3

You can use a nested NOT EXISTS to check this:

SELECT e.* 
FROM   events e 
WHERE NOT EXISTS(
    SELECT 1 FROM  client c 
    WHERE NOT EXISTS(
        SELECT 1 FROM eventclients ec 
        WHERE ec.eventid = e.id AND c.id= ec.clientid 
    )
)

Demo

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • @TheLight: It returns all events where all available clients are in eventclients with the given `eventid`. It's a little bit confusing because of the double negation. Side-note: `NOT EXISTS` is efficient and has no pitfalls(nullable columns) like other approaches. Have a look: http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join – Tim Schmelter Oct 28 '13 at 10:15
1

You can also do

SELECT e.id, e.name
  FROM events e LEFT JOIN eventclients ec
    ON e.id = ec.eventid LEFT JOIN clients c
    ON ec.clientid = c.id
 GROUP BY e.id, e.name
HAVING COUNT(ec.clientid) = 
(
  SELECT COUNT(*) FROM clients
)

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
0

Assuming that you mean event has a many-to-many relationship with client and event_client is the bridge table between them, then you should any of the many other questions asking about a SQL join across a many-to-many relationship.

SQL many to many select

How to filter SQL results in a has-many-through relation

Community
  • 1
  • 1
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154