How to fetch data only if both user are friend from sql server

Question

Here is my database structure. User table -user_id -fname -lname

profile table -profile_user_id -profile_id -profile_id

friend_request table -id -from_id -to_id -status my query is SELECT fname,lname,profile FROM user LEFT JOIN profile ON user.user_id = profile.profile_id WHERE(SELECT status FROM friend_request WHERE status = 'friend')

Please provide sample data, desired results and an approach database tag. A clear explanation of what you are trying to accomplish would also help. — Gordon Linoff, Jul 01 '21 at 11:29
Please be careful with undoing helpful formatting improvements by wellmeaning other users. I think the change you undid was a good one. Demonstrating that you do not appreciate people spending effort on improving your post is likely to be detrimental for finding somebody who helps you with your actual problem. — Yunnosch, Jul 01 '21 at 14:05
For the best way to represent a suitably tailored toy database have a look at https://stackoverflow.com/tags/sqlite/info and the MRE concept described there. Or at least at https://stackoverflow.com/editing-help — Yunnosch, Jul 01 '21 at 14:06

score 0 · Answer 1 · answered Jul 01 '21 at 06:11

Try like this:

 SELECT fname,lname,profile FROM user 
 INNER JOIN profile ON user.user_id = profile.profile_id 
 INNER join user 'friend' on friend.user_id = = profile.profile_id
 INNER JOIN friend_request ON friend_request.from_id = user.user_id and  friend_request.to_id = friend.user_id 
 WHERE status = 'friend'

How to fetch data only if both user are friend from sql server

1 Answers1