CODEWITHSUNDEEP
mysql
Shafizadeh
Shafizadeh

Reputation: 10340

How to use left join after where clause

I have two table:

// comment 
+----+---------+---------+----------------------+
| id | id_post | user_id |       content        |
+----+---------+---------+----------------------+
| 1  |  1111   |  2222   |   this is content1   |
| 2  |  3333   |  2222   |   this is content2   |
| 3  |  3333   |  4444   |   this is content3   |
+----+---------+---------+----------------------+

// user
+------+-----------+
|  id  |   name    |
+------+-----------+
| 2222 |   jack    |
| 4444 |   peter   |
+------+-----------+

And here is my query:

select c.content, u.name
  from comment c inner join user u on c.id_user = u.id
  where c.id_post = 3333;

And here is my result:

+----------------------+-----------+
|        content       |   name    |
+----------------------+-----------+
|   this is content2   |   jack    |
|   this is content3   |   peter   |
+----------------------+-----------+

So, My question: I need to add one more column contained 1 number but just for specific user. In other word I want this output:

// for this user: 2222 (or jack)
+----------------------+-----------+----------+
|        content       |   name    | user_id  |
+----------------------+-----------+----------|
|   this is content2   |   jack    |    1     |
|   this is content3   |   peter   |          |
+----------------------+-----------+----------+

// for this user: 444  (or peter)
+----------------------+-----------+----------+
|        content       |   name    | user_id  |
+----------------------+-----------+----------|
|   this is content2   |   jack    |          |
|   this is content3   |   peter   |     1    |
+----------------------+-----------+----------+

I think I should use one more left join, but I don't know how should I add one left join to my query. there is any suggestion ?

Upvotes: 1

Views: 445

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you just want a flag in the select:

select c.content, u.name,
       (u.id = 222) as hasUserId
from comment c inner join
    user u
    on c.id_user = u.id
where c.id_post = 3333;

I should note: the above returns 1 or 0. If you want 1 or NULL:

select c.content, u.name,
       (case when u.id = 222 then 1 end) as hasUserId

Upvotes: 1

Related Questions