Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Combine two queries into one

    Hi,

    I'm wondering if it's possible to have a single query to select two usernames from the same table (tblx) joined to another table (tbly) with two userid's on the same row? Here're some sample data:

    Code:
    tblx
    
    userid username
    1       john
    2       mary
    3       ryan
    4       joe
    
    tbly
    
    playerid mentorid
    1          2
    3          2
    3          4
    Currently, I need two queries to accomplish the selection:

    Code:
    1)
    SELECT username FROM tblx
    LEFT JOIN tbly ON tblx.userid=tbly.playerid
    
    2)
    SELECT username FROM tblx
    LEFT JOIN tbly ON tblx.userid=tbly.mentor
    Is it possible to combine the above two queries into one?

    Thanks in anticipation

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it is possible

    but i don't think you want an OUTER JOIN from tblx to tbly, unless you were trying to list all users whether or not they were a player, or whether or not they were a mentor

    i'm pretty sure you want INNER JOINs
    Code:
    SELECT playerx.username  as player_name
         , mentorx.username  as mentor_name
      FROM tbly
    INNER
      JOIN tblx as playerx
        ON playerx.userid = tbly.playerid
    INNER
      JOIN tblx as mentorx
        ON mentorx.userid = tbly.mentorid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    yes, it is possible

    but i don't think you want an OUTER JOIN from tblx to tbly, unless you were trying to list all users whether or not they were a player, or whether or not they were a mentor

    i'm pretty sure you want INNER JOINs
    Code:
    SELECT playerx.username  as player_name
         , mentorx.username  as mentor_name
      FROM tbly
    INNER
      JOIN tblx as playerx
        ON playerx.userid = tbly.playerid
    INNER
      JOIN tblx as mentorx
        ON mentorx.userid = tbly.mentorid
    Thanks r937

    I tried it on my code but it didn't work.

    I only have two tables but I see that you've 3:

    tbly
    mentorx
    playerx

    Am I missing something?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you have not seen a table alias used before

    i assure you that my sql will work for "tblx" and "tbly" as described

    of course, if your tables are really different than the ones you showed, then the onus for "translating" my sql into your actual scenario is on you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    perhaps you have not seen a table alias used before

    i assure you that my sql will work for "tblx" and "tbly" as described

    of course, if your tables are really different than the ones you showed, then the onus for "translating" my sql into your actual scenario is on you

    Thank you so much, r397

    I've seen table alias before, but I was an idiot to not have seen it in your code

    It works now, though I have to use LEFT JOIN instead of INNER JOIN.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, about having to use LEFT OUTER JOINs...

    this could only be necessary when there is a value of either playerid or mentorid in tbly which does not exist in tablx

    and this in turn makes me ask...

    1. how is this possible? (it should not be)

    2. do you know about foreign keys and what they're for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    okay, about having to use LEFT OUTER JOINs...

    this could only be necessary when there is a value of either playerid or mentorid in tbly which does not exist in tablx

    and this in turn makes me ask...

    1. how is this possible? (it should not be)

    2. do you know about foreign keys and what they're for?
    1)

    Both playerid and mentorid exist in tablx (because every userid - whether player or mentor, is associated with a username in tablx).

    In tbly, playerid may exist alone or playerid and mentorid may exist in the same row.

    2)

    In my sample data, the playerid and mentorid in tbly are foriegn keys. They are used to reference column values in tablx.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but that's the part i don't understand -- how could you have a player-mentor relationship with no mentor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    but that's the part i don't understand -- how could you have a player-mentor relationship with no mentor
    Because the mentor hasn't responded yet. I should probably have used different tables...one for storing the player's queries and one for storing the mentor's responses. Would that be a better approach?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the better approach would have been to explain the tables at the beginning

    your first post made tbly look like a many-to-many relationship table

    now you're saying you can have a player without a mentor

    this implies that not every user is a player

    all those nuances are vital to a full understanding of the problem, wouldn't you say?

    bottom line: you need one INNER JOIN and one LEFT OUTER JOIN, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    the better approach would have been to explain the tables at the beginning

    your first post made tbly look like a many-to-many relationship table

    now you're saying you can have a player without a mentor

    this implies that not every user is a player

    all those nuances are vital to a full understanding of the problem, wouldn't you say?

    bottom line: you need one INNER JOIN and one LEFT OUTER JOIN, right?
    Thanks, r937

    I'm not exactly sure how the results differ. I changed it to what you suggested and I'm getting what I expected (like before when both were LEFT JOIN)...

  12. #12
    Join Date
    May 2011
    Posts
    2

    Smile Answer

    SELECT a.username as USERNAME
    , b.username as USERNAME2


    FROM tblx a
    INNER
    JOIN tbly b
    ON a.userid = b.playerid

    INNER JOIN tbly c
    ON a.userid = c.mentor

    hope it will hepl you

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dear saheem, your query is almost exactly the same as the query that i gave in post #2 over three years ago, except yours is completely wrong

    try to keep up, would you?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2011
    Posts
    2
    Quote Originally Posted by r937 View Post
    dear saheem, your query is almost exactly the same as the query that i gave in post #2 over three years ago, except yours is completely wrong

    try to keep up, would you?

    Ohhh..I didn't see that.Any ways thanks....:

Posting Permissions

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