SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    alternatives for subqueries in MySQL

    I'm used to running queries in DB's like Oracle, SQL Server or even Access.

    Yes, I know that MySQL does not support subqueries but I would like to know what the alternatives are concerning the following queries.

    This query simply selects all members from a certain club :


    SELECT m.*
    FROM members AS m
    WHERE m.club = 108


    This query selects all members wich participate in a certain tournament (= p.tid) :


    SELECT *
    FROM members AS m, participants AS p
    WHERE m.nr = p.nr
    AND p.tid = 1


    Now I want all memers listed which DON'T participate in that tournament (from that particular club) ... normally i would use a simple subquery, however in MySQL that's a no can do.
    So I tried some alternative but it does not work properly :


    SELECT distinct m.*
    FROM members AS m, members AS m2, participants AS p
    WHERE m.club = 108
    AND m2.club = 108
    AND m.nr <> m2.nr
    AND m2.nr = d.nr
    AND p.tid = 1


    I still get all the members of club 108
    What am I missing here ?

    Thx in advance...

    Greettzz

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I forgot to mention that some members of that club are participants of that tournament so I should not see them all !!!

    Greettzz

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    > "Now I want all members listed which DON'T participate
    > in that tournament (from that particular club)"

    the query you want is a left outer join which selects only the unmatched rows
    Code:
    select m.*
      from members 
    left outer
      join participants
        on members.nr = participants.nr
       and participants.tid = 1
     where members.club = 108
       and participants.tid is null
    weird, eh? you're joining on tid=1 and testing tid is null -- what's up with that?

    for an explanation, please see OUTER JOIN instead of a NOT EXISTS subquery

    rudy

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works perfectly !!

    I read your explanation and it does make sense.. after reading it 5 times

    Thx alot

    Greettzz

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as long as participants.tid is defined as NOT NULL, if you EXPLAIN that query, you will see Not exists in the Extra column for the participants table. so i guess it's kinda treated like a NOT EXISTS clause in the query...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    > as long as participants.tid is defined as NOT NULL

    it would have to be, because for it to be allowed to go null would not make sense

    the way i understood quin's data structure, the participant table was a many-to-many relationship table between member and tournament

    this type of relationship table is very common, and has two foreign keys, one to each of its parents

    if you think about what it means to have a row in that relationship table with only the member key and the tournament key null, well, that would mean "this member is related to i-don't-know-which tournament" and in that case the row shouldn't be there!

    thus both the foreign keys should each be declared not null

    by the way, i have seen people declare a separate autonumber as the primary key for this relationship table, which is unnecessary -- the pair of foreign keys makes a better pk, and ensures that a specific member cannot participate in a specific tournament more than once

    and of course the pair can only be pk if both are not null

    rudy

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you're right. i meant that it's possible that participants.tid could not be NOT NULL (if it wasn't the PRIMARY KEY) and in that case EXPLAIN wouldn't say Not exists.


    Originally posted by r937
    by the way, i have seen people declare a separate autonumber as the primary key for this relationship table, which is unnecessary
    I KNOW! geez i have to roll my eyes every time i see that! even these "professional" scripts like vBulletin and Invision Board put an AUTO_INCREMENT column in a table that has a perfectly suitable PRIMARY KEY column(s). i call that AUTO_INCREMENT-itis (coined by forum member MattR). they think every table needs an auto-number column.

  8. #8
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 is correct..

    Sorry I did not specify the design of my tables..

    But the participants table consists indeed soly of 2 foreign key's, so NULL is out of the question :-)

    Greettzzz

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    2 fk's which are defined as NOT NULL btw..

    CREATE TABLE participants (
    &nbsp;&nbsp;&nbsp;&nbsp;nr int(5) NOT NULL, FOREIGN KEY (nr) REFERENCES members(nr),
    &nbsp;&nbsp;&nbsp;&nbsp;tid int(2) NOT NULL, FOREIGN KEY (tid) REFERENCES tournaments (tid)
    );


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
  •