CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 13

Find records from one table which don't exist in another and ignored in a third table

Based on using this SQL query I found on here I am looking to get results based on user_id. When I add this to the query I end up getting no results.

SQL - find records from one table which don't exist in another

I have three tables, one is content for my clients, two is what content the client agreed to and is using, and the third is content to be ignored.

The results I am looking to grab is content not being used or is not ignored.

available_content table
+----+
| id |
+----+
| 1  |
+----+
| 2  |
+----+
| 3  |
+----+
| 4  |
+----+
| 5  |
+----+

posted_content table
+----+----------+
| id | user_id  |
+----+----------+
| 1  | 123      |
+----+----------+
| 2  | 123      |
+----+----------+
| 3  | 456      |
+----+----------+

ignored_content table
+----+----------+
| id | user_id  |
+----+----------+
| 5  | 123      |
+----+----------+
| 1  | 456      |
+----+----------+
| 2  | 456      |
+----+----------+

SELECT a.id
FROM available_content AS a
LEFT OUTER JOIN posted_content AS b
ON (a.id = b.id) WHERE b.id IS NULL AND b.user_id = '123'

results
+----+
| id |
+----+
| 3  |
+----+
| 4  |
+----+

Upvotes: 1

Views: 52

Answers (1)

MK.
MK.

Reputation: 34597

SELECT a.id
FROM available_content a
LEFT OUTER JOIN posted_content b ON (a.id = b.id AND b.user_id = '123') 
LEFT OUTER JOIN ignored_content c ON (a.id = c.id AND c.user_id = '123') 
WHERE b.id IS NULL AND c.id IS NULL

Upvotes: 2

Related Questions