Atul Atul - 5 months ago 8
MySQL Question

MySQL: Putting condition in SELECT query based on column value

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_id
s

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)

Answer

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)
Comments