• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

MYSQL Query with subquery or join

i have a query which has several fields like relationshipid, userid1 and userid2

friendshipid|userid1|userid2
1|4|5
2|8|4
3|7|4
4|4|9

i get that data via select * from friendship where userid1=4 or userid2=4

in mycase userid = 4 is the one who initiated the friendship

what i need to do is find a way how i can get always the other userid like 5 in row one so i can query another table via subquery or a joint to get the name of the other party but not sure how to do this most effectively
0
AlexPonnath
Asked:
AlexPonnath
2 Solutions
 
johanntagleCommented:
You can use this as your subquery

select if(userid1=4,userid2,userid1) from friendship where userid1=4 or userid2=4;

Basically if userid1 equals the userid being searched, then userid2 must be the "friend userid", else userid1 is the friend.

I'm thinking this will really be used as a subquery, not as part of a join.
0
 
AlexPonnathAuthor Commented:
Yes that looks somewhat promissing but how could i use that to do a join, since i dont want 10 subqueries to get Name etc for that friend id..

lets say i want to join with usertable on the value from the query where usertable.userid = the userid from your query,
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You can use exactly that expression in the join

On if(...) = USERID
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now