mysql sql select and inner join for follower/followee names

Question

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 need to have some recursion and self referencing table. You coudl do it all in one table, and have it all in the person table, and have PersonIDParentID as a new column that references the ID in the same table, and do a recursive select (CTE in SQL would work, but not sure about mySQL). Then you could do as many child as you want with the recusion, you coudl have no or 100 nested levels — Brad, Nov 29 '18 at 14:58

score 1 · Accepted Answer · answered Nov 29 '18 at 14:58

1

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

answered Nov 29 '18 at 14:58

ScaisEdge

131,976
10
91
107

mysql sql select and inner join for follower/followee names

1 Answers1