MySQL JOIN Query Show Columns with IF ELSE Selected Option

user_14746379 0
Viewed 53

I have 2 Tables named users & selected_users. I have show the structure of tables with some dummy values below.

Table: users AS t1

+----+--------+
| id | name   |
+----+--------+
| 1  | A      |
| 2  | B      |
| 3  | C      |
| 4  | D      |
| 5  | E      |
+----+--------+

Table: selected_users

+----+------------+
| id | users_id   |
+----+------------+
| 1  | 2          |
| 2  | 5          |
+----+------------+

Desired Result:

+-------+----------+-----------+
| t1_id | t1_name  | selected  |
+-------+----------+-----------+
| 1     | A        | NO        |
| 2     | B        | YES       |
| 3     | C        | NO        |
| 4     | D        | NO        |
| 5     | E        | YES       |
+-------+----------+-----------+

What I have done:

I have written the following script.

SELECT t1.id AS t1_id, t1.name AS t1_name, CASE WHEN t2.t1_id=t1.id THEN 'YES' ELSE 'NO' END AS selected_in_t2
FROM t1
JOIN t2

But, Its showing me multiple values for each. Please help.

mysql
1 Answers

I did it using this Query.

SELECT t1.id AS t1_id, t1.name AS t1_name,
      CASE WHEN t2.id IS NULL THEN 'No' ELSE 'Yes' END as selected
FROM t1
LEFT JOIN t2
       ON t1.id = t2.t1_id ORDER BY t1.id
user_147463790
Related