1

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);
philipxy
  • 14,867
  • 6
  • 39
  • 83
user
  • 241
  • 1
  • 9
  • Thanks for the specification & code. Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. Give code for parts you can do. But-- (Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Jul 08 '19 at 18:14
  • Possible duplicate of [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/questions/27682228/how-to-get-matching-data-from-another-sql-table-for-two-different-columns-inner) – philipxy Jul 08 '19 at 18:16

1 Answers1

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786