I've looked at other examples of "data from one table not in other" SQL but still can't figure this out.
我已经看过其他“来自一个表中的数据而不是其他”SQL的例子,但仍然无法解决这个问题。
Table "pictures" contains:
表“图片”包含:
- "id", an auto increment ID number for this picture
- "owner", an ID number referring to a unique user
“id”,此图片的自动增量ID号
“所有者”,指向唯一用户的ID号
Table "ratings" contains:
表“评级”包含:
- "picture", a reference to an entry in the "pictures" table
- "userby", an ID number referring to a unique user
“picture”,对“图片”表中条目的引用
“userby”,指向唯一用户的ID号
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.
例如,我可能想要选择用户5未评级且不是所有者的所有图片。
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.
我怎样才能做到这一点?我希望尽可能避免使用子选择。谢谢。
2 个解决方案
#1
You need to add the additional checks to the join predicate and not in the where clause.
您需要将其他检查添加到连接谓词,而不是在where子句中。
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
#2
select *
from pictures as p
where p.owner <> 5
and not exists(select * from ratings where picture = p.id and userby = 5)
- first select pictures which is not owned by user
p.owner <> 5 - then search ratings for that picture by user
exists(subquery) - use not if need picture for which no rating shoul exists
首先选择不属于用户p.owner <> 5的图片
然后按用户搜索该图片的评级存在(子查询)
如果需要没有评级的图片,请使用
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.
如果有一些表包含用户没有评价某些图片的事实,那么就有可能! SQL可以使用仅存在的东西进行操作。这是不存在的(子查询) - 它意识到用户没有给图片评级的事实。
#1
You need to add the additional checks to the join predicate and not in the where clause.
您需要将其他检查添加到连接谓词,而不是在where子句中。
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
#2
select *
from pictures as p
where p.owner <> 5
and not exists(select * from ratings where picture = p.id and userby = 5)
- first select pictures which is not owned by user
p.owner <> 5 - then search ratings for that picture by user
exists(subquery) - use not if need picture for which no rating shoul exists
首先选择不属于用户p.owner <> 5的图片
然后按用户搜索该图片的评级存在(子查询)
如果需要没有评级的图片,请使用
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.
如果有一些表包含用户没有评价某些图片的事实,那么就有可能! SQL可以使用仅存在的东西进行操作。这是不存在的(子查询) - 它意识到用户没有给图片评级的事实。