VeeK VeeK - 1 year ago 57
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


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

*, AS annId
announcement a
announcement_view_count avc
avc.announcement_id =
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 Source

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:

    (avc.announcement_id is not NULL) wasRead
    announcement a
    announcement_view_count avc
    (avc.announcement_id = AND avc.user_id = 1)
    a.announce_on <= CURRENT_DATE
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download