I have three tables:

1. users: I need the username column of the row whose id column matches the uid argument for the query
2. matches: I need the id of the row, and one of liked or liker columns as uid. Meaning, if the uid the query gets as argument is let's say 34 and the liker is 34, I get the liked column, which could be 789 or whatever.
3. pic_url: it has several rows for one user, but I need only the row where profile_pic = 1 (which is only gonna be one) So far I've written this:

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

I don't know where to put the condition to filter the rows in the pic_urls table, so I get only the one where the profile_pic column is 1 (there's only one with this) The tables in question are:

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)

I want:

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

Where id and uid are columns from the matches table, username from users table, and profpic from pic_urls.

CodePudding user response：

This was the query I was after:

    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

CodePudding user response：

Please tell me what are the foreign keys above three table