MySQL连接三个表(一列或另一列基于条件)

Question

我有三个表：

1. users：我需要其id列与查询的uid参数匹配的行的username列
2. matches：我需要该行的id，以及liked或liker列中的一列作为uid。也就是说，如果查询作为参数得到的uid是34，而liker是34，那么我得到liked列，它可以是789或其他什么
3. pic_url：它为一个用户有几行，但我只需要profile_pic = 1所在的行(它将是一个(到目前为止，我已经写了以下内容：

SELECT
matches.id,
IF(matches.liker = ${uid}, matches.liked, matches.liker) AS matches.uid,
users.username,
pic_urls.url AS profilePic
FROM matches
JOIN users
ON matches.uid = users.id
JOIN pic_urls
ON users.id = pic_urls.user_id
WHERE profile_pic = 1

我不知道把过滤pic_urls表中行的条件放在哪里，所以我只得到profile_pic列为1的条件(只有一个(有问题的表格是：

mysql> describe matches;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| liker | int unsigned | NO   |     | NULL    |                |
| liked | int unsigned | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> describe users;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int unsigned | NO   | PRI | NULL    | auto_increment |
| username  | varchar(50)  | NO   |     | NULL    |                |
| firstname | varchar(50)  | NO   |     | NULL    |                |
...
mysql> describe pic_urls;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int unsigned | NO   | PRI | NULL    | auto_increment |
| user_id     | int unsigned | NO   |     | NULL    |                |
| url         | varchar(255) | NO   |     | NULL    |                |
| profile_pic | tinyint(1)   | YES  |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

我想要：

-------+-----------+-----------+-------------------+
|   id  |   uid     | username  |      profpic     |
+-------+-----------+-----------+------------------+
|   1   |   33      |  bob      |   someurl        |
|   5   |   22      |  frank    |   someurl        |
+-------+-----------+-----------+------------------+

其中，id和uid是来自matches表的列，username来自users表，profpic来自pic_urls。

Answer 1

这是我想要的查询：

SELECT
matches.id,
users.id AS uid,
users.username,
pic_urls.url
FROM matches
JOIN users ON IF(matches.liker = ${uid}, matches.liked, matches.liker) = users.id
JOIN pic_urls ON IF(matches.liker = ${uid}, matches.liked, matches.liker) = pic_urls.user_id
WHERE profile_pic = 1