Page 1 of 1

Doing a join to find out what doesnt match

Posted: Sun Aug 16, 2009 8:11 pm
by a94060
Hi everyone,

I have 2 table,1 called events and the other called attendence. events has an event_id and other fields. attendence has member_id and event_id. I am looking to write a query which will select me all entries in the events table which do not have a event_id/member_id combination in the attendence table.
Thank you
So far I have tried:

Code: Select all

SELECT events.event_name,events.event_id from events LEFT JOIN attendence on events.event_id != attendence.event_id  where attendence.member_id = 27
the problem is however, when the user does not have any entries in the attendence table, no events show using the query. If I do remove the where clause, all i get is a repetitive list of all the events available

Re: Doing a join to find out what doesnt match

Posted: Mon Aug 17, 2009 9:04 am
by tr0gd0rr
It sounds like you are trying to show which events a particular user has not attended. Is that right?

There is probably a more efficient way to do it (maybe with NOT EXISTS?), but here is one way that works:

Code: Select all

SELECT *
FROM events
WHERE id NOT IN (
  SELECT event_id
  FROM attendance
  WHERE member_id = 27
)

Re: Doing a join to find out what doesnt match

Posted: Mon Aug 17, 2009 9:17 am
by Eran

Code: Select all

SELECT events.event_name,events.event_id 
FROM events 
LEFT JOIN attendence ON events.event_id = attendence.event_id  
WHERE attendence.member_id != 27
 

Re: Doing a join to find out what doesnt match

Posted: Mon Aug 17, 2009 10:58 am
by a94060
tr0gd0rr wrote:It sounds like you are trying to show which events a particular user has not attended. Is that right?

There is probably a more efficient way to do it (maybe with NOT EXISTS?), but here is one way that works:

Code: Select all

SELECT *
FROM events
WHERE id NOT IN (
  SELECT event_id
  FROM attendance
  WHERE member_id = 27
)

Genius. Thank you very much. I did not know you could basically nest queries like that. The not in was also something i learned. Thank you