I have been struggling to come up with a way how to select all follower names and names of followees for each follower. My tables look as following
CREATE TABLE person
(
id int(10) auto_increment NOT NULL PRIMARY KEY,
name varchar(100) NOT NULL DEFAULT '',
);
INSERT INTO person (name) VALUES ('John');
INSERT INTO person (name) VALUES ('Alice');
INSERT INTO person (name) VALUES ('Eve');
INSERT INTO person (name) VALUES ('Edgar');
INSERT INTO person (name) VALUES ('Malorie');
CREATE TABLE follows
(
id int(10) NOT NULL DEFAULT '0',
fid int(10) NOT NULL DEFAULT '0'
);
INSERT INTO follows (id,fid) VALUES (1,2);
INSERT INTO follows (id,fid) VALUES (1,3);
INSERT INTO follows (id,fid) VALUES (1,4);
INSERT INTO follows (id,fid) VALUES (2,1);
INSERT INTO follows (id,fid) VALUES (2,5);
INSERT INTO follows (id,fid) VALUES (3,2);
INSERT INTO follows (id,fid) VALUES (5,2);
INSERT INTO follows (id,fid) VALUES (5,1);
So far i have come up with statement like this, but obviously it is not working as needed
SELECT person.name FROM person INNER JOIN follows ON (person.id = follows.id)
How can i make query that selects both, folower and folowee names his following in one query?
The expected result should be like this
+---------+---------+
| folower | folowee |
+---------+---------+
| John | Eve |
| John | Alice |
| John | Malorie |
| Alice | John |
| Alice | Eve |
| Eve | Alice |
+---------+---------+
you couold use the join on person twice one for person and one for follower name
SELECT person.name , follower.name
FROM person
INNER JOIN follows ON (person.id = follows.id)
INNER JOIN person as follower on follows.fid = follower.id