Combine two queries into one

pearl2 · #1 (**permalink**) 12-09-07, 07:57

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

r937 · #2 (**permalink**) 12-09-07, 08:36

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

pearl2 · #3 (**permalink**) 12-10-07, 05:20

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?

r937 · #4 (**permalink**) 12-10-07, 07:46

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

pearl2 · #5 (**permalink**) 12-10-07, 21:18

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.

r937 · #6 (**permalink**) 12-10-07, 22:21

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?

pearl2 · #7 (**permalink**) 12-11-07, 04:12

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.

r937 · #8 (**permalink**) 12-11-07, 06:18

but that's the part i don't understand -- how could you have a player-mentor relationship with no mentor

pearl2 · #9 (**permalink**) 12-11-07, 08:29

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?

r937 · #10 (**permalink**) 12-11-07, 08:53

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?

pearl2 · #11 (**permalink**) 12-12-07, 02:18

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)...

saheem · #12 (**permalink**) 05-19-11, 04:51

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

r937 · #13 (**permalink**) 05-19-11, 05:48

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?

saheem · #14 (**permalink**) 05-19-11, 05:56

Quote:

Originally Posted by r937

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....:

Internet Services

Web Development

Digital Marketing

Consumer Tech