Doing a join to find out what doesnt match

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
a94060
Forum Regular
Posts: 543
Joined: Fri Feb 10, 2006 4:53 pm

Doing a join to find out what doesnt match

Post 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
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Doing a join to find out what doesnt match

Post 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
)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Doing a join to find out what doesnt match

Post 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
 
User avatar
a94060
Forum Regular
Posts: 543
Joined: Fri Feb 10, 2006 4:53 pm

Re: Doing a join to find out what doesnt match

Post 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
Post Reply