CODEWITHSUNDEEP
mysqlsqljoin
David
David

Reputation: 165

Mysql join where specific record in one table not in other

I've looked at other examples of "data from one table not in other" SQL but still can't figure this out.

Table "pictures" contains:

Table "ratings" contains:

I want to select all pictures which have no entry in the ratings table by a specific user AND where the picture owner is not that user.

For example I might want to select all pictures which user 5 has not rated and is not the owner of.

Usually this would be a join between pictures and ratings and check if the ratings record is null, but I can't seem to get the addition of doing it only for specific users right.

How can I do this? I want to avoid sub-selects if possible. Thank you.

Upvotes: 0

Views: 76

Answers (2)

skazska
skazska

Reputation: 421

select * 
from pictures as p
where p.owner <> 5
  and not exists(select * from ratings where picture = p.id and userby = 5)
  1. first select pictures which is not owned by user p.owner <> 5
  2. then search ratings for that picture by user exists(subquery)
  3. use not if need picture for which no rating shoul exists

Unfortunately result you need could not be produced by 1 step combination (without subselect), because to do so an operation required, that can combine something existent (any picture not owned by user ) with something nonexistent ( missing rating by user ).

If there were some table containing fact that user did not rate some picture, then it would be possible! SQL can operate with things that exists only. That is what not exists(subquery) do - it realizes fact that there are no ratings given by user to a picture.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

You need to add the additional checks to the join predicate and not in the where clause.

So something like

SELECT *
FROM pictures p LEFT JOIN
ratings r ON p.ID = r.PictureID AND r.UserID = 5 
WHERE r.ID IS NULL
AND p.OwnerID <> 5

Have a look e this example

SQL Fiddle DEMO

Upvotes: 3

Related Questions