1

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  |
+----+
Community
  • 1
  • 1
Tim
  • 13
  • 2

1 Answers1

2
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
MK.
  • 33,605
  • 18
  • 74
  • 111