检查连接的结果集是否包含SQL中的某些值

Question

我有一个类似如下的数据库结构：

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

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

我希望创建一个查询，在这个查询中，我可以返回属于给定用户的所有资产，并返回一个布尔值，该布尔值指示该资产是否为他们的“最爱”。

我可以这样做，count()等于零意味着它不是最受欢迎的(但它看起来很老套，效率很低)：

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;

有没有什么好的方法来做这个查询？

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

LEFT JOIN偏好资产，如果当前没有偏好记录(u.asset_id IS NULL)，则它不是偏好，否则它就是偏好。

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