在SQL中检查联结结果集是否包含某个值

Question

我有一个类似于这样的数据库结构：

asset
+----+---------+
| id | user_id |
+----+---------+

user_favorite
+----------+---------+
| asset_id | user_id |
+----------+---------+

我希望创建一个查询，可以返回属于给定用户的所有资产，并指示它是否为他们的“收藏夹”。

我可以这样做，其中计数（）等于零表示它不是收藏夹（但似乎很麻烦和低效）：

select distinct(a.asset_id), 
    (select count(*) 
     from user_favorite f 
     where f.user_id = MY USER ID 
       and f.asset_id = a.asset_id) 
from asset a 
left join user_favorite u on a.asset_id=u.asset_id 
where a.user_id = MY USER ID;

我尝试了以下方法（但在多个用户收藏时，它会从资产中产生多个条目）：

select distinct (a.asset_id), 
    (u.user_id in (MY USER ID)) 
from asset a 
left join user_favorite u on a.asset_id=u.asset_id 
where a.user_id = MY USER ID;

我也尝试了这个（但IN条件没有被满足）:

select distinct(a.asset_id), 
    (u.user_id in (MY USER ID)) 
from asset a 
left join user_favorite u on a.asset_id=u.asset_id 
where a.user_id = MY USER ID group by u.user_id;

有没有一种好的方法来执行这个查询？

- shauntarves

---

是的，我刚刚注意到你似乎简化了问题的模式。Asset表实际上是一个UserAsset表吗？否则，为什么要选择DISTINCT，除非标记为“id”的列实际上不是ID。 - OGHaza

这是一个简化的模式。抱歉。asset.id并不需要DISTINCT，并且它是user_favorite.user_id中引用的id。 - shauntarves

Answer 1

这是我做的方式，但我相信有许多可接受的方法：

SELECT DISTINCT a.asset_id 
    ,CASE WHEN u.asset_id IS NULL 
          THEN 0 
          ELSE 1 
     END AS IsFavourite
FROM asset a
LEFT JOIN user_favourite u ON a.asset_id = u.asset_id 
                           AND a.user_id = u.user_id
WHERE a.userid = MY_USER_ID

将收藏夹表 favourites 左连接到资产表 assets，如果没有收藏记录存在 (u.asset_id IS NULL)，则不是收藏，否则是收藏。

- OGHaza

---

你提到了 a.asset_id，尽管该列似乎只被称为 id，因此我仍然坚持使用它，因为这样更清晰明了。 - OGHaza

第二个连接条件是关键。谢谢！ - shauntarves

哈哈，我一开始忘了这个，花了几分钟才注意到，没问题。 - OGHaza

Answer 2

或许这能帮上一点忙

select 
     assetid,
     case when isnull(fav.user_id,0) =1 Then 0 else 1 end
form
     asset a
     left outer join 
     user_favourite fav
     on a.user_id = fav.user_id
where
     fav.id = 'foobar'
GROUP BY assetid, fav.user_id

- AffenkingLouie