SQL - Select records where records don't exist...

[toastyman]

Yeah, a weird title for a weird problem...

users
user_id | firstname | lastname


users_attendance
attendance_id | user_id | thing_id


things_recent
recent_id | user_id | thing_id


Basically I want to select users.firstname, users.lastname, things_recent.thing_id where users.user_id = '2', and the user doesn't have a record in users_attendance.

How the heck do you do a 'Select records from a table, where there isn't a matching record in another table' query?! I've tried researching the different types of JOINs available, but they all seem to be the opposite of what i'm after..

Any ideas / advice would be appreciated

 

[~J~]

use NOT.

EG:

SELECT Name FROM dbo.users WHERE dbo.users.user_ID NOT IN (SELECT user_id FROM dbo.users_attendance)

 

[Visage]

Yeah, a weird title for a weird problem...

users
user_id | firstname | lastname


users_attendance
attendance_id | user_id | thing_id


things_recent
recent_id | user_id | thing_id


Basically I want to select users.firstname, users.lastname, things_recent.thing_id where users.user_id = '2', and the user doesn't have a record in users_attendance.

How the heck do you do a 'Select records from a table, where there isn't a matching record in another table' query?! I've tried researching the different types of JOINs available, but they all seem to be the opposite of what i'm after..

Any ideas / advice would be appreciated

select users.firstname, users.lastname, things_recent.thing_id where users.user_id = '2' and users.user_id not in (select user_id from users_attendance)

 

[toastyman]

Aha job done! Another useful SQL command i've learnt today

Cheers to you both!

 

[PsychoDuck]

This is how I would do it, using a left outer join which may give better query performance depending on the indexes of the tables.

select u.firstname, u.lastname, t.thing_id
from users u
inner join things_recent t on u.user_id = t.user_id
left outer join users_attendance ua on u.user_id = ua.user_id
where u.user_id = 2 and ua.user_id is null

The trick is to add a ua.user_id is null into the where clause which will return only records which are in users and things_recent and not in users_attendance.

 

[Trimbo]

This is how I would do it, using a left outer join which may give better query performance depending on the indexes of the tables.

I would agree with this however any semi-decent RDBMS (not sure if MySql falls into this category or not) should be able to spot and optimise the non-correlated subquery (the not in) to be as good a sif you had written it a an outer join.