Blog

I was posed a very trivial question from one of my friends recently. It picked my interest, since I could not readily solve it, and I had the inkling that there is a better, simple solution for this problem.

The problem was, let’s say I have a table of users as below

And these users have friends as below

or more easier to read as

Now, the trivial task was find common friends for users 1, and 5, hence naturally the answer should be

User 1 : Woody’s friends, and User 5 : Andy’s friends  are

and

So, the question I had was what could be the best SQL query that would be return me this result?

I thought of 2 different SQL queries

SELECT f.fid, u.name 'Woody and Andy\'s common friends' FROM
(SELECT f.`fid` FROM friends f
WHERE f.`uid` IN (1,5)
GROUP BY f.`fid` HAVING COUNT(f.`fid`) > 1) f INNER JOIN user u ON (u.uid=f.fid)

-or- another one was

SELECT f.fid, u.name 'Woody and Andy\'s common friends' FROM
(SELECT a.fid FROM
(SELECT * FROM friends f
WHERE f.`uid`=1) a INNER JOIN
(SELECT * FROM friends f
WHERE f.`uid`=5) b ON (a.fid=b.fid)) f INNER JOIN user u ON (u.uid=f.fid)

Both seem to work, but is there any elegant way of getting common friends in a much simpler SQL query? At this moment, I logically find first SQL query more intuitive since all it does is pickup friends who appear more than once when users are 1 and 5. The second SQL query seems plain brute force approach.