Combing multiple joins in SQL

Combing multiple joins in SQL
0

#1

So here we have 3 tables,

  1. students
  2. student_projects
  3. project_pairs

Let’s say that I am getting interviewed by another dev and they ask me to write a sequel that returns this result:

/*
Carrotapault|Mad Hattery
Carpet Physics|Hyena Habitats
*/

And the tables are here:

CREATE TABLE students (id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    phone TEXT,
    birthdate TEXT);

INSERT INTO students (first_name, last_name, email, phone, birthdate)
    VALUES ("Peter", "Rabbit", "[email protected]", "555-6666", "2002-06-24");
INSERT INTO students (first_name, last_name, email, phone, birthdate)
    VALUES ("Alice", "Wonderland", "[email protected]", "555-4444", "2002-07-04");
INSERT INTO students (first_name, last_name, email, phone, birthdate)
    VALUES ("Aladdin", "Lampland", "[email protected]", "555-3333", "2001-05-10");
INSERT INTO students (first_name, last_name, email, phone, birthdate)
    VALUES ("Simba", "Kingston", "[email protected]", "555-1111", "2001-12-24");
    
CREATE TABLE student_projects (id INTEGER PRIMARY KEY,
    student_id INTEGER,
    title TEXT);
    
INSERT INTO student_projects (student_id, title)
    VALUES (1, "Carrotapault");
INSERT INTO student_projects (student_id, title)
    VALUES (2, "Mad Hattery");
INSERT INTO student_projects (student_id, title)
    VALUES (3, "Carpet Physics");
INSERT INTO student_projects (student_id, title)
    VALUES (4, "Hyena Habitats");
    
CREATE TABLE project_pairs (id INTEGER PRIMARY KEY,
    project1_id INTEGER,
    project2_id INTEGER);

INSERT INTO project_pairs (project1_id, project2_id)
    VALUES(1, 2);
INSERT INTO project_pairs (project1_id, project2_id)
    VALUES(3, 4);

And the answer is here:

SELECT a.title, b.title FROM project_pairs

JOIN student_projects a

ON a.id = project_pairs.project1_id

JOIN student_projects b

ON b.id = project_pairs.project2_id

What is my thought process should be?
Here is what I did in order to understand this question:

After that I have no idea where should I start to solve this question.

Could anyone list out their thought? Line by line, step by step?
If you are asking to write a query that output the result, what are your thought process?

Like

  1. i am gonna select project1_id and project2_id
  2. all of those things are displayed by title , so I could not just select * from project_pairs
  3. etc

@RandellDawson
Thanks in advanced.


#2

Just to be clear, they want pairs of results with each item in a pair separated by | and each pair separated by a line return?


#3

no no , I added those line…
the whole 3 min video is here:

I sort of get what she is saying, but if you ask me to do it, I don’t know where to start.
Sorry if I was unclear.