Mysql select statement with multiple terms / conditions
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 build a single query to get this result below, for example opponents of Fabiano:
output:
+--------+-----------+
| gameId | userName |
+--------+-----------+
| 1 | magnus |
| 3 | alexander |
| 4 | veselin |
+--------+-----------+
Edit1:
This was what I was trying and I was unable to get them in one request:
- select opponents fabiano [select * from games where 2 in (userId1, userId2);]
- read each of the lines and check which ones are fabiano (2) and choose a different userId
- from the userIds of these opponents, get their name from the 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;
source to share
You can combine the two datasets together, as well as all games where Fabiano is User 1, with all the games he plays as 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, let's assume that Fabiano cannot be User1
and at the same time User2
, since we will need to consider UNION ALL vs UNION DISTINCT :)
This can also be removed a bit:
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
source to share
Doing this without the UNION clause would make it more efficient
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
source to share
This request should work with all 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;
source to share