CODEWITHSUNDEEP
mysql
Bobby Wan-Kenobi
Bobby Wan-Kenobi

Reputation: 925

MySQL join three tables(one column or another based on condition)

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.

Upvotes: 0

Views: 37

Answers (1)

Bobby Wan-Kenobi
Bobby Wan-Kenobi

Reputation: 925

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

Upvotes: 1

Related Questions