mysql select statement with multiple where/conditions

Question

I have the following two tables in mysql:

users:

+--------+-----------+
| userId | userName  |
+--------+-----------+
| 1      | magnus    |
| 2      | fabiano   |
| 3      | alexander |
| 4      | veselin   |
+--------+-----------+

games:

+--------+---------+---------+
| gameId | userId1 | userId2 |
+--------+---------+---------+
| 1      | 1       | 2       |
| 2      | 1       | 3       |
| 3      | 2       | 3       |
| 4      | 2       | 4       |
+--------+---------+---------+

How can I construct a single query such that I get this below output of say, fabiano's opponents:

output:

+--------+-----------+
| gameId | userName  |
+--------+-----------+
| 1      | magnus    |
| 3      | alexander |
| 4      | veselin   |
+--------+-----------+

Edit1:

This was what I was trying and I wasn't able to get them into a single query:

select fabiano's opponents [select * from games where 2 in (userId1, userId2);]
read each of the rows, and check which of them is fabiano(2), and select the other userId
from the userIds of these opponents, get their name from users table

Edit2: Inspired by the answers below, I wrote this (they work):

-- NO JOIN
select x.gameId, users.userName from
(
select gameId, userId2 as id from games where userId1=2
UNION
select gameId, userId1 as id from games where userId2=2 
) as x, users 
where users.userId = id;

-- NO JOIN, NO UNION        
select x.gameId, users.userName from (
SELECT g.gameId,
    CASE WHEN userId1 = 2
            THEN userId2     
         WHEN userId2 =2
            THEN userId1
         END AS id
FROM games g) as x, users
where users.userId = id;

Use a query to get opponents as userId1, write the same query to get opponents as userId2, and use a union to combine the two queries. — JB Nizet
– JB Nizet, Commented Jan 3, 2015 at 8:20

StuartLC · Accepted Answer · 2015-01-03 08:19:45Z

4

You can union the two sets of data together, viz all games where Fabiano is User 1, with all games that he is in the role of User 2:

SELECT x.Opponent
FROM
(
    SELECT u.Name AS Opponent
    FROM games g
    INNER JOIN users u
    ON g.userId2 = u.UserId
    WHERE g.UserId1 = 2 -- Fabiano

    UNION

    SELECT u.Name
    FROM games g
    INNER JOIN users u
    ON g.userId1 = u.UserId
    WHERE g.UserId2 = 2 -- Fabiano
) AS x;

At this point as assume that Fabiano can't simultaneously both be User1 and User2, as we would need to consider UNION ALL vs UNION DISTINCT :)

This could also be tidied up a bit into:

SELECT x.Opponent
FROM
(
    SELECT u.Name AS Opponent, g.UserId1 AS PlayerId
    FROM games g
    INNER JOIN users u
    ON g.userId2 = u.UserId

    UNION

    SELECT u.Name, g.UserId2 AS PlayerId
    FROM games g
    INNER JOIN users u
    ON g.userId1 = u.UserId
) AS x
WHERE x.PlayerId = 2; -- Fabiano

answered Jan 3, 2015 at 8:19

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kaya Toast Over a year ago

Thanks. Based on your answer, I wrote a simple, single query which is relatively easy for a beginner like me to read - I'm adding it to my question.

StuartLC Over a year ago

Glad to be of help. Apologies, I see I left off gameId

SMA · Accepted Answer · 2015-01-03 08:25:37Z

2

Try something like:

SELECT `gamess`.gameId, `users`.userName
FROM users INNER JOIN
   (SELECT gameId, userId2 as userId
    FROM games
    WHERE userId1 = 2
    UNION
    SELECT gameId, userId1 as userId
    FROM games
    WHERE userId2 = 2) AS gamess
ON `gamess`.userId = `users`.userId

answered Jan 3, 2015 at 8:25

SMA

37.2k8 gold badges53 silver badges76 bronze badges

Comments

Jas · Accepted Answer · 2016-09-28 04:51:08Z

1

Doing this without a UNION clause will make it more performant

SELECT g.gameid,
CASE WHEN u1.userid = 2 -- fabino* 
            THEN u2.username
            else u1.username END AS Opponent
FROM games g
LEFT JOIN users u1
ON g.userId1 = u1.UserId
LEFT JOIN users u2
on g.userid2 = u2.userid
WHERE (g.UserId1 = 2 OR g.userid2 = 2) -- fabino

edited Sep 28, 2016 at 4:51

answered Jan 3, 2015 at 8:34

Jas

5461 gold badge7 silver badges13 bronze badges

Comments

Hassan Ali · Accepted Answer · 2015-01-03 08:21:55Z

0

SELECT us.* FROM users us INNER JOIN games gs ON us.userId = gs.userId1 GROUP BY us.userId ,us.userName

answered Jan 3, 2015 at 8:21

Hassan Ali

731 silver badge8 bronze badges

Comments

Mihai · Accepted Answer · 2015-01-03 09:19:38Z

0

This query should be works all of common userid

   SELECT x.Opponent
  FROM
  ( 
    SELECT u.userName AS Opponent
    FROM games g
    INNER JOIN users u
    ON g.userId2 = u.UserId
    WHERE g.UserId1 in (select UserId from users where UserId in (select userid1 from games)) 
    UNION
    SELECT u.userName
    FROM games g
    INNER JOIN users u
    ON g.userId1 = u.UserId
    WHERE g.UserId2 in (select UserId from users where UserId in (select userid2 from games))  
  ) AS x;

edited Jan 3, 2015 at 9:19

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

answered Jan 3, 2015 at 9:05

user2515605

72 bronze badges

Collectives™ on Stack Overflow

mysql select statement with multiple where/conditions

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related