mysql - Check if joined result set contains some value in SQL -

i have database structure similar this:

asset +----+---------+ | id | user_id | +----+---------+  user_favorite +----------+---------+ | asset_id | user_id | +----------+---------+ 

i looking create query can return assets belonging given user , boolean indicating whether or not "favorite" them.

i can this, count() equalling 0 mean it's not favorite (but seemed hacky , inefficient):

select distinct(a.asset_id),      (select count(*)       user_favorite f       f.user_id = user id         , f.asset_id = a.asset_id)  asset  left join user_favorite u on a.asset_id=u.asset_id  a.user_id = user id; 

i tried (but yielded multiple entries assets when multiple users had favorited them:

select distinct (a.asset_id),      (u.user_id in (my user id))  asset  left join user_favorite u on a.asset_id=u.asset_id  a.user_id = user id; 

i tried (but in condition wasn't respected):

select distinct(a.asset_id),      (u.user_id in (my user id))  asset  left join user_favorite u on a.asset_id=u.asset_id  a.user_id = user id group u.user_id; 

is there way query?

this how i'd it, i'm sure there many acceptable ways:

select distinct a.asset_id      ,case when u.asset_id null            0            else 1       end isfavourite asset left join user_favourite u on a.asset_id = u.asset_id                             , a.user_id = u.user_id a.userid = my_user_id 

left join favourites assets, , if there no favourite record present (u.asset_id null) not favourite, otherwise is.