Solved

MYSQL Query with subquery or join

Posted on 2013-01-29
3
531 Views
Last Modified: 2013-01-29
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
Comment
Question by:AlexPonnath
3 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 300 total points
ID: 38834014
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
 

Author Comment

by:AlexPonnath
ID: 38834085
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 38834115
You can use exactly that expression in the join

On if(...) = USERID
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question