MySQL query where a pair of values does not exist in another table
Question by Evan Johnson
I’m trying to preform a SELECT query that grabs data from two tables only if certain data does not exist in a third table. Here are the three tables:
Blogs
id | blog_name
Featured Feeds
id | blog_id
Subscriptions
id | member_id | blog_id
I want to return the blog_id and blog_name of those listed in the Featured Feeds table, but exclude Featured Feeds that the user (member_id) is already subscribed to in the Subscriptions table. Here is the query I have thus far (using 3 for the member_id – please note the table names are slightly different than above):
SELECT featured_feeds.blog_id, featured_feeds.category_id, blogs.blog_name FROM featured_feeds LEFT JOIN blogs ON featured_feeds.blog_id = blogs.blog_id LEFT JOIN subscriptions ON subscriptions.blog_id = featured_feeds.blog_id WHERE subscriptions.blog_id != '3' ORDER BY blogs.blog_name ASC
This query doesn’t exclude the Featured Feeds that the user is subscribed to if another user is subscribed to the same feeds. For example, if Subscriptions table had the following rows:
1 | 3 | 4
2 | 2 | 4
If blog_id 4 is listed in the featured feeds, my query will still return that blog_id due to the second row of data above. Do I need to use a GROUP?
Thanks in advance!
Answer by hkf
SELECT b.id, b.blog_name
FROM blogs b
INNER JOIN featured f on f.blog_id = b.id
WHERE b.id NOT IN (SELECT id FROM subscriptions WHERE member_id = 3)
Answer by Starx
Do a outer a left join, omitting the matching results.
SELECT * FROM `featured` f
LEFT OUTER JOIN `subscription` s
ON f.blog_id = s.blog_id
WHERE s.id IS null
