Kaya Toast Kaya Toast - 2 months ago 7
MySQL Question

mysql select statement with multiple where/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 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;

Answer

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