CODEWITHSUNDEEP
mysql
Snedden27
Snedden27

Reputation: 1930

struggling with a sql query join

I am struggling with writing a query join in mysql

I have two table

Challenges

challenge_ID(int) |to_user(int)|from_user(int)|timestamp|gameID=>nullable

Users

iduser(int)|first_name(string)

I want get the first name of to_user and form_user when I have the challengeID

for instance if

Challenges

challenge_ID(int) |to_user(int)|from_user(int)|timestamp|gameID
  1                9            10              sometimestamp

Users

   iduser(int)|first_name(string)
   9             Tom
   10            Chris
   11            Patrick

I would like to get 'Tom' and 'Chris' for challenge id 1

Thanks for your help.

Upvotes: 1

Views: 33

Answers (4)

Wilianto Indrawan
Wilianto Indrawan

Reputation: 2404

You can try this query

SELECT u_to.first_name, u_from.first_name
FROM challenges c
INNER JOIN users u_to ON u_to.iduser = c.to_user
INNER JOIN users u_from ON u_from.iduser = c.from_user
WHERE c.challange_ID = 1

Upvotes: 0

fthiella
fthiella

Reputation: 49089

It looks like you need a UNION and then a JOIN:

SELECT users.first_name
FROM (
  SELECT from_user AS usr FROM challenges WHERE challenge_id=1
  UNION
  SELECT to_user FROM challenges WHERE challenge_id=1
) u INNER JOIN users ON u.usr = users.id_user

UNION will remove duplicates on the subquery, if there are no duplicates you can use UNION ALL which is faster.

Upvotes: 0

Hassan Al Bourji
Hassan Al Bourji

Reputation: 195

this is how you do this

select c.* ,u.first_name as to_name , u2.first_name as from_name 
FROM challenges c 
join users u on c.to_user = u.id
join users u2 on c.from_user = u2.id
where c.challenge_ID = 1

Upvotes: 0

genespos
genespos

Reputation: 3311

It may be something like this:

SELECT first_name
FROM Users 
WHERE iduser IN (SELECT to_user
               FROM challenges
               WHERE Challenge_Id = 1
              UNION
               SELECT from_user
               FROM challenges
               WHERE Challenge_Id = 1)

Upvotes: 1

Related Questions