0

I have two tables:

// users
+----+--------+
| id |  name  |
+----+--------+
| 1  | Jack   |
| 2  | Peter  |
| 3  | John   |
| 4  | Barman |
| 5  | Ali    |
+----+--------+

// friends
+---------+-----------+
| user_id | friend_id |
+---------+-----------+
| 1       | 3         |
| 1       | 4         |
| 1       | 5         |
| 3       | 1         |
| 3       | 2         |
| 3       | 4         |
| 5       | 2         |
+---------+-----------+
-- both user_id and friend_id columns refer to the id column of users table

I want to select all friends of Jack (id = 1). So here is the query:

select * from friend where user_id = 1
/* output
| 1       | 3         |
| 1       | 4         |
| 1       | 5         |
*/

Now I also want to select friends of Jack's friends. How can I do that?

Note, I don't want to select duplicate rows. So I want this output: 

/* expected output:
| 1       | 3         |
| 1       | 4         |
| 1       | 5         |
| 3       | 2         |
| 3       | 4         |
| 5       | 2         |
*/
stack
  • 10,280
  • 19
  • 65
  • 117
  • 1
    Please add what you have tried so far – Jens Jan 31 '17 at 15:55
  • the data structure is potentially problematic. You don't need to store the 1 -> 3 and 3 -> 1 mappings separately. One record in the database to represent this relationship is sufficient. – ADyson Jan 31 '17 at 15:59
  • duplicate? http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – Kostya Jan 31 '17 at 15:59
  • @ADyson I would argue that the opposite is true. This is a standard method for reciprocation. – Strawberry Jan 31 '17 at 16:08
  • @Strawberry matter of opinion I guess, but it depends on exactly what it's representing and whether there's other metadata associated. If you think of a friendship network as a graph where the friends table represents the edges (and users the nodes), then really you should only need one record to represent an edge. if it's possible for person A to be "friends" with person B, and simultaneously person B is friends with person A, then two records might be used... – ADyson Jan 31 '17 at 16:31
  • @Strawberry ...but only if there's also the logical possibility that A is friends with B but simultaneously B is _not_ friends with A. To me that second scenario doesn't sound very realistic. If B merely hasn't "approved" the friendship yet, that can just be a boolean flag in the record. If the relationship in one particular direction has other specific properties, then that's a different matter but there's no indication of that here, at least from the info given. Also the OP mentioned removing "duplicates" and that 3 -> 1 seemed to be the only record missing from the desired output. – ADyson Jan 31 '17 at 16:33
  • @ADyson That's another standard approach, but the OP's method is valid, and, while there is redundancy, it's only 2n - which is modest. – Strawberry Jan 31 '17 at 16:38

2 Answers2

2

Add a IN clause with all friends of Jack use distinct user_id, friend_id

select distinct f1.user_id, f1.friend_id
from friend f1
where user_id = 1
      or 
      user_id in (select f2.friend_id
                  from friend f2
                  where user_id = 1);
McNets
  • 10,352
  • 3
  • 32
  • 61
1
select distinct 
    f2.* 
from 
    friend f1, 
    friend f2 
where 
    f1.user_id = 1 and 
    (f1.friend_id = f2.user_id or f2.user_id = 1)

This still includes duplicates with opposite directions (it considers A--friend-->B as not being the same as B--friend-->A)

Lud
  • 482
  • 2
  • 9