Join two tables but still get full data of first table

Question

First I have table users

+---------+----------+------------------+
| user_id | username |      email       |
+---------+----------+------------------+
|       1 | User 1   | email1@gmail.com |
|       2 | User 2   | email2@gmail.com |
|       3 | User 3   | email3@gmail.com |
|       4 | User 4   | email4@gmail.com |
+---------+----------+------------------+

Next I have table user_announcement

+---------+----------+---------+-----------+
| user_id | annou_id | is_read | read_time |
+---------+----------+---------+-----------+
|       1 |        1 |       0 | Time      |
|       2 |        1 |       1 | Time      |
|       1 |        2 |       0 | Time      |
|       2 |        3 |       1 | Time      |
+---------+----------+---------+-----------+

I am trying to figure how to get all user and their read status for announcement id 1 I want output have something like this

+---------+----------+------------------+----------+---------+-----------+
| user_id | username |      email       | annou_id | is_read | read_time |
+---------+----------+------------------+----------+---------+-----------+
|       1 | User 1   | email1@gmail.com | 1        | 0       | Time      |
|       2 | User 2   | email2@gmail.com | 1        | 1       | Time      |
|       3 | User 3   | email3@gmail.com | NULL     | NULL    | NULL      |
|       4 | User 4   | email4@gmail.com | NULL     | NULL    | NULL      |
+---------+----------+------------------+----------+---------+-----------+

I tried all kinds of join but it didn't give me the result I want.

@Barmar - I think this is not duplicate. OP updated the question. — Utsav, Dec 24 '15 at 09:48

Barmar · Accepted Answer · 2015-12-24T10:13:12.930

2

When doing a LEFT JOIN, if you need to filter on a column in the second table, you should do it in the ON clause.

SELECT *
FROM user u
LEFT JOIN user_announcement ua
ON u.user_id=ua.user_id AND ua.annou_id = 1

edited Dec 24 '15 at 10:13

answered Dec 24 '15 at 09:49

Barmar

741,623
53
500
612

Yes, the way I did just cause a trouble. I tried your query and it seems work fine. Still testing on it. – Thuong Nguyen Dec 24 '15 at 10:12
Your way only shows the rows that have `annou_id = 1` or don't exist at all in the announcements table. You have to do it this way to get `NULL` for the rows that have announcements with annou_id != 1. – Barmar Dec 24 '15 at 10:14

score 1 · Answer 2 · 2015-12-24T09:42:29.923

Edit: Updated answer as per user requirement

This is what you are looking for. Here I am first getting min(annou_id) for each user and then getting other records for that row. Now treating this derived table as second table, I am going for a left join as earlier to get the required records.

select u.user_id,u.username,u.email,ua_derived.annou_id,ua_derived.is_read,ua_derived.read_time
from user u
left join 
    (
            select ua1.user_id,ua1.annou_id,ua1.is_read,ua1.read_time from user_announcement ua1
        inner join 
            (select user_id,min(annou_id) as annou_id  from user_announcement
            group by user_id 
            ) ua2
        on ua1.user_id=ua2.user_id
        and ua1.annou_id=ua2.annou_id
    ) ua_derived
on u.user_id=ua_derived.user_id;

SQL Fiddle demo here

http://sqlfiddle.com/#!9/57a74/4

========================================================================

Prev Answer:

Answer is same as Yeldar, but you just need to use column alias to display.

select u.user_id,u.username,u.email,ua.annou_id,ua.is_read,ua.read_time
from user u
left join user_announcement ua
on u.user_id=ua.user_id

SQL Fiddle demo here

http://sqlfiddle.com/#!9/db28e/1

Sorry, I edited my question a bit. It won't come out well when I have more than one user_id = 1 in table `user_announcement` — Thuong Nguyen, Dec 24 '15 at 09:20
So in this case, do you need minimum of `annou_id` if you have more than 1 `user_id`? — , Dec 24 '15 at 09:28

score 0 · Answer 3 · answered Dec 24 '15 at 09:02

0

According to w3schools,

The LEFT JOIN keyword returns all rows from the left table, with the matching rows in the right table. The result is NULL in the right side when there is no match.

That's exactly what you need:

SELECT * FROM `users` AS u
LEFT JOIN `user_announcement` AS ua
ON u.user_id = ua.user_id

answered Dec 24 '15 at 09:02

Yeldar Kurmangaliyev

33,467
12
59
101

Sorry, I edited my question a bit. It won't come out well when I have more than one user_id = 1 in table `user_announcement` – Thuong Nguyen Dec 24 '15 at 09:20

score 0 · Answer 4 · edited Dec 24 '15 at 13:07

0

yes you can try this

select
   t1.*,
   t2.annou_id,
   t2.is_read,
   t2.read_time
from
   user t1
   left join user_announcement t2
      on t1.user_id = t2.user_id
where
   t2.annou_id = 1
order by
   t1.user_id desc

edited Dec 24 '15 at 13:07

Henk van Boeijen

7,357
6
32
42

answered Dec 24 '15 at 12:32

ashish mulani

197
1
14

Join two tables but still get full data of first table

4 Answers4