CODEWITHSUNDEEP
mysqljoin
Jimmery
Jimmery

Reputation: 10119

MYSQL: Join two tables on one field or another depending on a condition

I am trying to join two tables on one field or another, depending on the values of those fields.

Here is my situation, I have a friends Database with User IDs in two columns. For example:

uid1    uid2
------- -------
tom001  bob001
bob001  paul001

In this example bob001 is friends with both tom001 and paul001 (and vice versa).

Now I am trying to get a list of friends for a certain user and then join that with the Users Database, so I can get at the users name, picture, etc.

My question is, how can I join two tables using a value that could be in uid1 or uid2 but not both? In this example, if I wanted to show bob001's friends, I would have to join the two tables first on the value in uid1 and then on the value in uid2.

Is there a way in SQL to ignore one of these User IDs and use the other? Can I somehow merge the values of these fields together, stripping out the original User ID?

Or can I use an IF statement to get at the correct User ID?

TIA

Upvotes: 1

Views: 365

Answers (1)

Abkarino
Abkarino

Reputation: 1446

If I understood you correctly, here is the MYSQL query. Just to make the MYSQL easier to follow create the following view.

 create view allfriends as select uid1,uid2 from tablename 
 union select uid2,uid1 from tablename;

Then this select

 select uid2 from allfriends where uid1 = 'bob001';

Upvotes: 2

Related Questions