I'm having problems with a complicated MySQL query:
One table contains rows of ALL id's (project id's), and another table contains "project_ID"s of projects that have been viewed. So I need to get all of the ID's that have NOT been viewed by that user, but may have possibly been viewed by other users.
This is the code query which fails at the moment:
SELECT p.id FROM projects p
LEFT JOIN projects_viewed pv ON p.id = pv.project_id
WHERE NOT pv.username = 'SomeOneElse';
It would work, but I think it maybe failing because p.username can be equal, as well as not equal, to "SomeOneElse" because "pv.project_id" is not unique as mentioned earlier.
Here is an example given that the initial values are as followed:
INSERT INTO `projects` (`id`) VALUES
(1), (2), (3), (4), (5);
INSERT INTO `projects_viewed` (`project_id`, `username`,) VALUES
(1, 'Billy'),
(2, 'SomeOneElse'),
(2, 'Billy'),
(3, 'Billy'),
(4, 'SomeOneElse'),
(4, 'Billy'),
(5, 'Billy'),
If the username was "SomeOneElse" then I want to return all id's from the projects table IF no matches between id (from the projects table) and project_id (from the projects_viewed table) are found at all, or the only ones that are in projects_viewed have a username which is not the username we're querying with ("SomeOneElse" in this case).
So given this example, only id's 1, 3, and 5 should be returned.