MySQL: Putting condition in SELECT query based on column value

Question

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)

Mostafa Vatanpour · Accepted Answer · 2016-06-28 11:16:40Z

1

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)

edited Jun 28, 2016 at 11:16

answered Jun 28, 2016 at 6:32

Mostafa Vatanpour

1,44816 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Atul Over a year ago

This works perfect. Only thing is we need to say select distinct t1.* otherwise it returns redundant rows (especially when privacy is set false)

Blank · Accepted Answer · 2016-06-28 06:29:19Z

1

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

edited Jun 28, 2016 at 6:29

answered Jun 28, 2016 at 6:22

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Comments

KaeL · Accepted Answer · 2016-06-28 10:03:49Z

1

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

Use LEFT JOIN so even if user_privacy is false, the query will still return the user_profiles
Add condition in the ON clause of LEFT JOIN indicating that it will return the user_friends if user_privacy is true.
Move the filtering of F.friends_id in the ON clause of the LEFT JOIN. (Thanks to @Msf vtp for verifying, cheers!)

edited Jun 28, 2016 at 10:03

answered Jun 28, 2016 at 6:25

KaeL

3,6612 gold badges31 silver badges59 bronze badges

10 Comments

Mostafa Vatanpour Over a year ago

When F.friends_id according left join is null then "F.friends_id = 2001" is false so the answer is not correct.

KaeL Over a year ago

@Msfvtp If F.friends_id is null, then either P.user_privacy == false or user_friends table is empty

Mostafa Vatanpour Over a year ago

I know. Assume P.id=1997 and its user_privacy=false then your where clause is false (because "F.friends_id = 2001" will return false) and this row will not be in the result.

KaeL Over a year ago

I believe the row from users_profiles will still be there. Please verify your assumption and share us your results. You can use sqlFiddle if you want.

Mostafa Vatanpour Over a year ago

I created sqlfiddle : sqlfiddle.com/#!9/4e91a0/1 and compared there my answer and your answer.

|

Collectives™ on Stack Overflow

MySQL: Putting condition in SELECT query based on column value

3 Answers 3

1 Comment

Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related