umm.. a sort of anti-join?

Spudhead · #1 (**permalink**) 05-12-09, 09:40

Another dumb question....

Code:

SELECT e.eventName
FROM events e
INNER JOIN eventCharities ec ON e.idEvents = ec.idEvents
INNER JOIN charities c ON ec.idCharities = c.idCharities
INNER JOIN userCharities uc ON uc.idCharities = c.idCharities
WHERE uc.idUsers = 42

That will get me all events that are associated with one of the user's favourite charities.

However, I also have a table "userEvents" (guess what that does) - I'd like to amend the query above so that it DOESN'T include the events that the user is already taking part in.

The only way I can think of is to plonk a subquery on the bottom. This seems a bit... clunky. Is there a better way?

Code:

SELECT e.eventName
FROM events e
INNER JOIN eventCharities ec ON e.idEvents = ec.idEvents
INNER JOIN charities c ON ec.idCharities = c.idCharities
INNER JOIN userCharities uc ON uc.idCharities = c.idCharities
WHERE uc.idUsers = 42
AND events.idEvents NOT IN (SELECT idEvents FROM userEvents WHERE idUsers = 42)

Thanks

mike_bike_kite · #2 (**permalink**) 05-12-09, 10:13

It would be faster if you used "not exists" rather than "not in" :

Code:

SELECT e.eventName
FROM events e
INNER JOIN eventCharities ec ON e.idEvents = ec.idEvents
INNER JOIN charities c ON ec.idCharities = c.idCharities
INNER JOIN userCharities uc ON uc.idCharities = c.idCharities
WHERE uc.idUsers = 42
AND NOT EXISTS (
     SELECT 1 FROM userEvents ue 
     WHERE  ue.idUsers = uc.idUsers 
            and ue.idEvents = events.idEvents )

doesn't make it any prettier though.

Mike

PS It's probably better to supply a simple spec in words rather than SQL as readers 1st have to decode your SQL and then try and imagine it done in a different way rather than the way they've just seen it written - which is difficult.

Spudhead · #3 (**permalink**) 05-12-09, 10:41

Cheers - yes, I should have realised that EXISTS would have been faster. And advice duly noted.

Internet Services

Web Development

Digital Marketing

Consumer Tech