CODEWITHSUNDEEP
sql
Karlo
Karlo

Reputation: 1708

What is wrong with this JOIN statement in SQL?

I am working on this tutorial about SQL. In Step 2 of 2, it is asked to use the JOIN command to link 2 people from the persons list using information from the friends list. I edited the provided code and obtained the code included below: the relevant part starts with select persons.fullname, persons2.fullname. However, the last paragraph of the code ('relevant code') does not yield any result nor error message. Am I doing something wrong?

Code

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");
INSERT INTO persons (fullname, age) VALUES ("Fluffy Sparkles", "8");

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 (1, "coding");
INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
INSERT INTO hobbies (person_id, name) VALUES (2, "coding");
INSERT INTO hobbies (person_id, name) VALUES (3, "skating");
INSERT INTO hobbies (person_id, name) VALUES (3, "rowing");
INSERT INTO hobbies (person_id, name) VALUES (3, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (4, "coding");
INSERT INTO hobbies (person_id, name) VALUES (4, "dilly-dallying");
INSERT INTO hobbies (person_id, name) VALUES (4, "meowing");

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);

/* personal contribution starts here

select persons.fullname,hobbies.name
    from persons
    join hobbies
    on hobbies.person_id=persons.id;

/* select persons.fullname, persons2.fullname
    from persons
    join persons persons2
    join friends
    on persons.fullname=friends.person1_id and persons2.fullname=friends.person2_id; */

/* relevant code: */
select persons.fullname, persons2.fullname
    from persons
    join friends
    on persons.fullname=friends.person1_id
    join persons persons2
    on persons2.fullname=friends.person2_id;

[tutorial (c) khanacademy.org]

Upvotes: 1

Views: 965

Answers (1)

Dour High Arch
Dour High Arch

Reputation: 21711

join friends
on persons.fullname=friends.person1_id

You're only including friends whose person1_id equals a person's fullname. Since person1_id is an integer and fullname is text, those will never be equal. You probably want:

select persons.fullname, persons2.fullname
from persons
join friends
on persons.id=friends.person1_id
join persons persons2
on persons2.id=friends.person2_id;

Upvotes: 3

Related Questions