SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot Steveiwonder's Avatar
    Join Date
    Nov 2008
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query help (its doing my head in)

    Hallo Guys,

    As it says in the title i need help with a particular query (the only query i have EVER had problems with!)

    I'll explain what i'm trying to get from two tables & the tables.

    Table gn_attendees.

    Columns:
    game_id
    game_date
    user_id

    Table users.

    Columns:
    user_id(PKEY)
    nick
    fname

    This is how it comes into my head.

    Code MySQL:
    SELECT users.nick, users.fname 
    FROM users 
    WHERE users.user_id != gn_attendees.user_id
    AND
    gn_attendees.game_id = '4';

    So now i will explain what i really what.

    I need to select EVERY user from the users table, where their user_id does NOT appear in gn_attendees where the game_id is = 'X' (X being a number)

    So if the user_id has added them to self to game_id i don't want there details from the user table only whom has NOT added there self to gn_attendees.

    I hope this makes sense and i would be extemly greatful if someone could help me out by pointing out my mistakes.

    Thanks

    Steve

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You need a sub query to find those in gn_attendees that you don't want

    Code:
    SELECT users.nick, users.fname
    FROM users
    WHERE users.user_id NOT IN (SELECT gn_attendees.user_id 
                               FROM gn_attendees 
                               WHERE gn_attendees.game_id = '4');
    your attempt tried to use data from a table that was not mentioned in the list of tables in the FROM clause.

  3. #3
    SitePoint Zealot Steveiwonder's Avatar
    Join Date
    Nov 2008
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works a treat. I feel ashamed i did not know about the NOT IN clause :/

    but thank you!

    Its been a big help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    there is another way to do it, which is the direction you were going in with the "not equals" join
    Code:
    SELECT users.nick
         , users.fname 
      FROM users 
    LEFT OUTER
      JOIN gn_attendees
        ON gn_attendees.user_id = users.user_id 
       AND gn_attendees.game_id = 4
     WHERE gn_attendees.user_id IS NULL
    the query uses LEFT OUTER JOIN to attempt to find matching rows for a particular game_id, but selects only those users where the match is not found (the IS NULL test)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Steveiwonder's Avatar
    Join Date
    Nov 2008
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would there be a better one out of the two example above?

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The latter is better. the use of IN list, while working, is notoriously slow.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    they produce the same result and so theoretically there should be no difference

    in theory, theory and practice are the same, but in practice, they aren't

    you could put the word EXPLAIN in front of each of those queries, and study what you get

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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