Can’t find a solution to a problem relating three tables and join operation

So, I am not able to solve the following problem. Three tables are given: tables

I need to write a code using join so that all pairs friend pairs are listed with their full names. I only know that there may be a need for two joins one of them being self join. I tried to join a persons table with friends table, but the result in one column was name and the other was and id, I have no idea how to make two consecutive joins so that each name has a corresponding friends’ name. Here are the table information:

CREATE TABLE persons (
id INTEGER PRIMARY KEY AUTOINCREMENT,
fullname TEXT,
age INTEGER);



INSERT INTO persons (fullname, age) VALUES ("Bobby McBobbyFace", "12");
INSERT INTO persons (fullname, age) VALUES ("Lucy BoBucie", "25");
INSERT INTO persons (fullname, age) VALUES ("Banana FoFanna", "14");
INSERT INTO persons (fullname, age) VALUES ("Shish Kabob", "20");

CREATE table hobbies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person_id INTEGER,
name TEXT);

INSERT INTO hobbies (person_id, name) VALUES (1, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
INSERT INTO hobbies (person_id, name) VALUES (3, "skating");
INSERT INTO hobbies (person_id, name) VALUES (4, "coding");

CREATE table friends (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person1_id INTEGER,
person2_id INTEGER);

INSERT INTO friends (person1_id, person2_id)
VALUES (1, 4);
INSERT INTO friends (person1_id, person2_id)
VALUES (2, 3);

Answer

Are you just looking for two joins?

select p1.fullname, p2.fullname
from friends f join
     persons p1
     on f.person1_id = p1.id join
     persons p2
     on f.person2_id = p2.id;

Advertisement

Answer