MySQL Query Help

Question

Heh guys,
I need help with a MySQL Query. The Output of the query should look like this

User1       User2
xxx1        xxx2
xxx3        xxx1

but not

User1       User2
xxx1        xxx2
xxx2        xxx1

and so on.

I need all "friendships" between different users. A friendship exists i.e. when

UserId 8 exists in Column User1_id  
UserId 4 exists in Column User2_id  
**AND**
UserId 8 exists in Column User2_id  
UserId 4 exists in Column User1_id 

Thanks in Advance!

Friendship Table

+----------+----------+
| User1_id | User2_id |
+----------+----------+
|        8 |        4 | 
|        4 |        8 | 
|       29 |        4 | 
|        4 |       10 | 
|       10 |        4 | 
|        8 |       37 | 
|        4 |       29 | 
|       37 |        8 | 
|       37 |        4 | 
|       29 |        8 | 
|        4 |       37 | 
|        8 |       10 | 
|        8 |       29 | 
|        4 |       40 | 
|       40 |        4 | 
|       40 |       29 | 
|       29 |       40 | 
+----------+----------+

User Table

+----+-----------------------------+
| id | username                    |
+----+-----------------------------+
|  4 | hhessel                     | 
|  8 | xxx1                        | 
| 10 | xxx2                        |  
| 29 | xxx3                        | 
| 40 | xxx4                        | 
| 37 | xxx5                        | 
| 39 | xxx6                        | 
+----+-----------------------------+

Answer 1

You can try:

select u1.username un0, u2.username un1
  from friendship f inner join user u1 on f.user1_id = u1.id
 inner join user u2 on f.user2_id = u2.id
  left join
   (
     select u2.username un0, u1.username un1
       from friendship f inner join user u1 on f.user1_id = u1.id
      inner join user u2 on f.user2_id = u2.id
   ) b using (un0, un1)
 where un0 is null

Not sure if it will work.

Answer 2

Use:

SELECT a.user1_id, 
       a.user2_id
  FROM FRIENDSHIP a
  JOIN FRIENDSHIP b ON b.user2_id = a.user1_id
                   AND b.user1_id = a.user2_id
                   AND b.user1_id > a.user1_id

You'll have to reverse the user1_id comparison if you want the column values reversed:

AND b.user1_id < a.user1_id

Answer 3

This should get you going:

SELECT    f1.user1_id, f1.user2_id
FROM      friendship f1
LEFT JOIN friendship f2 ON f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id
WHERE     f1.user1_id = f2.user2_id

You could always join to the User table if you need actual user names, etc.