Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: umm.. a sort of anti-join?

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Cheers - yes, I should have realised that EXISTS would have been faster. And advice duly noted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •