You will have to run one query per degree of separation.
I'll assume the following schema (SQLite dialect):
CREATE TABLE person (
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY ( id )
);
CREATE TABLE friend (
person INTEGER NOT NULL,
friend INTEGER NOT NULL,
PRIMARY KEY ( person, friend )
);
Then your graph would be
With this you can figure out a person's friends by joining the tables:
sqlite> SELECT P1.name, P2.name
...> FROM person P1
...> JOIN friend F ON P1.id = F.person
...> JOIN person P2 ON F.friend = P2.id
...> WHERE P1.name = 'Bob' ;
P1.name|P2.name
Bob|Al
Bob|Doug
Bob|Gary
For the friends' friends you use a subselect:
sqlite> SELECT P1.name, P2.name
...> FROM person P1
...> JOIN friend F ON P1.id = F.person
...> JOIN person P2 ON F.friend = P2.id
...> WHERE P1.id IN (
...> SELECT F.friend
...> FROM person P
...> JOIN friend F ON P.id = F.person
...> WHERE P.name = 'Bob'
...> ) ;
P1.name|P2.name
Al|Bob
Al|Chuck
Al|Frank
Al|Ibrahim
Doug|Bob
Doug|Chuck
Doug|Hamza
Gary|Bob
Gary|Hamza
Gary|Ibrahim
And so on and so forth, analogously:
sqlite> SELECT P1.name, P2.name
...> FROM person P1
...> JOIN friend F ON P1.id = F.person
...> JOIN person P2 ON F.friend = P2.id
...> WHERE P1.id IN (
...> SELECT F.friend
...> FROM person P
...> JOIN friend F ON P.id = F.person
...> WHERE P.id IN (
...> SELECT F.friend
...> FROM person P
...> JOIN friend F ON P.id = F.person
...> WHERE P.name = 'Bob'
...> )
...> );
Bob|Al
Bob|Doug
Bob|Gary
Chuck|Al
Chuck|Doug
Chuck|Ed
Chuck|Frank
Frank|Al
Frank|Chuck
Hamza|Doug
Hamza|Gary
Ibrahim|Al
Ibrahim|Gary
I used SELECT P1.name, P2.name at the top level SELECT for demonstration purposes. In practice you will want SELECT DISTINCT P2.name.
Makeshifts last the longest. |