VeeK VeeK - 16 days ago 5
MySQL Question

MYSQL - Select all rows that don't join with other table

I have two tables, first stores the announcements and second stores the list of users that have read the announcements. I'm trying to capture all announcements with status of read or unread. If the second table doesnot contain a row of user_id corresponding with that announcement_id, it's unread. Here's how it looks like

Announcements

id | content | announce_on
1 | foo | 2016-11-10
2 | bar | 2016-11-11
3 | zim | 2016-11-12


Announcement View Count

id | user_id | announcement_id
1 | 1 | 1
2 | 1 | 2
3 | 2 | 1
4 | 2 | 3


(user 1 has read announcement 1 and 2, user 2 has read announcement 1)

The best thing I've got so far is

SELECT
*,
a.id AS annId
FROM
announcement a
LEFT JOIN
announcement_view_count avc
ON
avc.announcement_id = a.id
WHERE
a.announce_on <= CURRENT_DATE AND (avc.user_id = 1 OR avc.user_id IS NULL)


The problem is that I will not get announcement 3 at all. The
avc.user_id IS NULL
part is not working right. Announcement 3 is not showing up because it has been viewed by user 2.

It's hard to explain but I want to load all anouncements and have one column that could tell me if the announcement has been viewed by a particular user. (who's id is available). Can someone give me a hint?

I've also tried NOT EXISTS and NOT IN but they return empty results.

Answer

I think I understand what you need. Lets see: you need the list of all announcements with an additional column saying if the announcement have been read by an specific user. In that case you need to add the filter for the user id inside the join condition and not as a global condition in the where clause. You can use something like this:

SELECT
    a.*,
    (avc.announcement_id is not NULL) wasRead
FROM
    announcement a
LEFT JOIN
    announcement_view_count avc
ON
    (avc.announcement_id = a.id AND avc.user_id = 1)
WHERE
    a.announce_on <= CURRENT_DATE
GROUP BY a.id