问题描述:

I have two tables user_profiles and user_friends.

user_profiles has columns id, user_privacy and few other columns (like username, age etc).

user_friends has columns user_id and friends_id. One user_id can have multiple friend_ids

This query simply returns profile of user having id, say, 1997:

select * from user_profiles

where prfls.id=1997;

And this query returns profile of user having id 1997 only when it has got friend having id, say, 2001:

select * from user_profiles prfls

inner join user_friends frnds on (prfls.id=frnds.user_id)

where prfls.id=1997 and frnds.friends_id=2001;

However, I want to write a single query that will check if column user_privacy (in user_profiles) for user 1997 is false then the query shouldn't check for friends_id in user_friends. It should simply return profile of user 1997. But if the user_privacy is true then only it should check for it.

How can this query be written? (Preferably using joins and without using sub-queries)

网友答案:

Use left join for it:

select distinct t1.* from user_profiles t1
 left join user_friends t2 on(t1.id = t2.user_id)
 where t1.id = 1997 and 
  (user_privacy='false' or t2.friends_id = 2001)
网友答案:

Try this;)

select t1.*
from user_profiles t1
where t1.id = 1997
and (
     t1.user_privacy = 'false'
     or exists (select 1 from user_friends t2 where t1.id = t2.user_id and t2.friends_id = 2001)
    )

Without subquery, you can try this;)

select distinct t1.*
from user_profiles t1
inner join user_friends t2
on t1.id = 1997 and (t1.user_privacy = 'false' or (t1.id = t2.user_id and t2.friends_id = 2001))
网友答案:
SELECT *
    FROM user_profiles P
    LEFT JOIN user_friends F ON F.user_id = P.id
        AND P.user_privacy = true
        AND F.friends_id = 2001
    WHERE P.id = 1997
  1. Use LEFT JOIN so even if user_privacy is false, the query will still return the user_profiles
  2. Add condition in the ON clause of LEFT JOIN indicating that it will return the user_friends if user_privacy is true.
  3. Move the filtering of F.friends_id in the ON clause of the LEFT JOIN. (Thanks to @Msf vtp for verifying, cheers!)
相关阅读:
Top