Mutual friends

[Manixat]

Hello great minds,

 

I turn to you yet again.

 

Table:

 

sender	recipient
1	2
1	3
1	4
2	3
2	4
2	5

 

 

In my poor attempts to overwhelm the impossible, for me, endeavor to return the mutual friends of users X and Y I have gotten to the point where hope is my only option. Anyhow if I wanted to get a hold of the mutual friends I thought I had to get both users' friends and compare them. This is what I've got and as you might have already figured it is no good:

 

SELECT * FROM
	((SELECT `sender`,`recipient` FROM `friendships` WHERE (`sender`='X' OR `recipient`='X') AS f1) JOIN
        (SELECT `sender`,`recipient` FROM `friendships` WHERE (`sender`='Y' OR `recipient`='Y') AS f2))

 

This is just the attempt to retrieve 2 tables with friends, which surprisingly ( not really ) gives me an error which I cannot understand. Any help?

 

Here's the error message, if it's of any use

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS f1) JOIN (SELECT `sender`,`recipient` FROM `friendships` WHERE (`sen' at line 2

Edited April 7, 2013 by Manixat

[Barand]

try removing  the ) after f1 and f2

[Manixat]

try removing  the ) after f1 and f2

 

Still gives me the same error, as well as this:

 

SELECT * FROM
	SELECT `sender`,`recipient` FROM `friendships` WHERE (`sender`='4' OR `recipient`='4') AS f1 JOIN
        SELECT `sender`,`recipient` FROM `friendships` WHERE (`sender`='6' OR `recipient`='6') AS f2

 

Also tried this, which turned out no good as well

SELECT * FROM
	(SELECT `sender`,`recipient` FROM `friendships` WHERE (`sender`='4' OR `recipient`='4') AS f1 JOIN
        SELECT `sender` as snd,`recipient` as rcp FROM `friendships` WHERE (snd='6' OR rcp='6') AS f2)

Edited April 7, 2013 by Manixat

[Manixat]

Ok I have gotten it to a point where things are starting to work out. I have a new question:

How can I form a table of only the friends of a certain user with one column ?

 

It would look something like this:

 

SELECT * FROM 
	(SELECT `sender` FROM `friendships` WHERE `recipient`='X') AS f1,
        (SELECT `recipient` FROM `friendships` WHERE `sender`='X') AS f2

 

but instead of splitting it into 2 columns I want to return all records in a single column?

Edited April 7, 2013 by Manixat

[Barand]

use a UNION instead of a JOIN

[Manixat]

use a UNION instead of a JOIN

.

Exactly! Voila finally nailed it, it still kinda returns 2 columns but it doesn't really matter cuz they're the same. Here's the final working query if anyone needs it in future

 

SELECT * FROM
	(SELECT `sender` FROM `friendships` WHERE `recipient`='x'
        UNION 
        SELECT `recipient` FROM `friendships` WHERE `sender`='x') as dgd
        INNER JOIN
	(SELECT `sender` FROM `friendships` WHERE `recipient`='y'
        UNION 
        SELECT `recipient` FROM `friendships` WHERE `sender`='y') as dgd2
        ON dgd.sender = dgd2.sender
        

Edited April 7, 2013 by Manixat