Check if joined result set contains some value in SQL

Question

I have a database structure similar to this:

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

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

I am looking to create a query where I can return all assets belonging to a given user AND a boolean indicating whether or not it is a "favorite" for them.

I can do this, where a count() equalling zero would mean it's not a favorite (but it seemed hacky and inefficient):

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;

I tried this (but it yielded multiple entries from assets when multiple users had favorited them:

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;

I also tried this (but the IN condition wasn't respected):

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;

Is there some good way to do this query?

Answer 1

This is how I'd do it, but I'm sure there are many acceptable ways:

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 favourites to assets, and if there is no favourite record present (u.asset_id IS NULL) then it is not a favourite, otherwise it is.