CODEWITHSUNDEEP
mysqlsqljoinunion
Corey Thompson
Corey Thompson

Reputation: 398

SQL Union or other table join

I'm having trouble wrapping my head around an SQL query, trying to figure out something.

Just quickly, here is my (simple) table: It's a representation of friends added in a social network site. I have to use the table format like this, user1 and user2 are both primary keys.

          User1       |       User2
    --------------------------------------
      [email protected]       |   [email protected]
      [email protected]       |   [email protected]
      [email protected]|   [email protected]
      [email protected]       |   [email protected]
      [email protected]       |   [email protected]

What I need to do is write a SELECT statement that will return all unique users that are friends with [email protected] for example.

SELECT User2
FROM members 
WHERE User1 = '[email protected]'

Would return [email protected] and not [email protected], even though I need it to return the latter.

SELECT *
FROM members
WHERE User1 = '[email protected]'
OR User2 = '[email protected]'

Would return an error I think? Something with selecting multiple columns probably.

So I figure a union join or some other join is necessary (union so no duplicates?) but I'm really not sure how to go about doing it.

I'm trying to implement this in PHP, so even if the last query I wrote worked, I'm not sure how to echo User1 or User2 depending on which it returned/which one I needed if that makes sense.

Upvotes: 0

Views: 150

Answers (3)

Harsh Gupta
Harsh Gupta

Reputation: 4538

Use an alias name for the column 

SELECT User2 AS friend
FROM members 
WHERE User1 = '[email protected]'
UNION
SELECT User1 AS friend
FROM members 
WHERE User2 = '[email protected]'

Now you can echo friend.

HTH

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

Using a UNION (for performance) :-

SELECT User2
FROM members
WHERE User1 = '[email protected]'
UNION
SELECT User1
FROM members
WHERE User2 = '[email protected]'

Upvotes: 2

Andrei Dascalu
Andrei Dascalu

Reputation: 1177

It all depends on how you define the relationship between the two users.

For example, you have a table entry where User1 = '[email protected]' and User2='[email protected]'. What does it mean? Does it mean that user foo became friends with mumu? Does it matter the direction of the 'friendship'?

If the answer is no an you simply want to fetch all rows where either user is foo, then your query

    SELECT *
FROM members
WHERE User1 = '[email protected]'
OR User2 = '[email protected]'

is correct.

However, to fetch the data you need to explore both columns for results.

Upvotes: 0

Related Questions