Do I need to "join" or write a subquery?

Discussion in 'MySQL' started by OnlyOneN, Dec 4, 2009.

0
  1. #1
    I'm developing an event registration system that requires attendees to choose a roommate. The simplified attendee table structure is:

    id, first_name, last_name, roommate_id

    When the user enters the page, a drop-down is populated with names where the roommate_id is null or empty. That works great, but if someone has chosen a roommate, I don't want to let anyone else choose that roommate.

    Sample table:

    1, Jane, Doe, 2
    2, Alice, Smith, <blank>
    3, Sandy, Brown, <blank>

    Jane Doe chooses Alice Smith as her roommate. She submits the form and all is good.

    Sandy Brown enters the form and sees that Alice Smith is available to choose, because her roommate_id field is empty. However, Jane Doe already chose her.

    I currently have a server-side validation running that makes sure the roommate_id column is unique, and that works fine. However, if I can eliminate the round-trip to the server to verify that the roommate_id column is unique, that'd be great.

    Which leads me to the question: What would the SQL query look like that returns all people that are available to be chosen as a roommate? (i.e. roommate_id is empty, and id is not in any row's roommate_id field)

    Thanks!
    Glen
     
    OnlyOneN, Dec 4, 2009 IP
  2. iama_gamer

    iama_gamer Active Member

    Messages:
    404
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #2
    i would suggest updating the roommate_id of the room mate chosen also but for some reason if you want to leave it empty u can try something on these lines

    SELECT a.first_name, a.last_name
    FROM attendee a
    WHERE a.id NOT
    IN (

    SELECT b.roommate_id
    FROM attendee b)
    AND a.`roommate_id` =0
    LIMIT 0 , 300
     
    iama_gamer, Dec 4, 2009 IP
  3. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #3
    What happens if Alice chooses Sandy As her roommate? And then Sandy chooses someone else? Are they bunking 4 to a room?
     
    plog, Dec 4, 2009 IP
  4. OnlyOneN

    OnlyOneN Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thanks gamer, I'll try that...


    Plog, I have two queries that run when the page loads... one to get possible roommates, and one to see if they've been picked as a roommate. If they've been picked, it populates the dropdown with ONLY one person... the person who picked them.

    Thanks!

    Glen
     
    OnlyOneN, Dec 4, 2009 IP
  5. iama_gamer

    iama_gamer Active Member

    Messages:
    404
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #5
    let me know if that works. If you get some error then include the details of it ... i think the query should work by itself
     
    iama_gamer, Dec 4, 2009 IP