CODEWITHSUNDEEP
sqlsqlite
tHatpart
tHatpart

Reputation: 1136

Accessing relationship in sqlite

Given the following table schemas and assuming everyone only has 1 friend, how can I access the friends name in SQL? The following query returns id, name, and salary, but I want to return each friend as well. I'm very new to sql so these relationships are extremely confusing at the moment, any help would be greatly appreciated. Thank you.

select s.id, s.name, i.salary from students as s join incomes as i on s.id = i.id;

table schema

Upvotes: 0

Views: 26

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

Assuming each friend also be a student, you need to join students -> friends -> students:

SELECT
    s1.id,
    s1.name,
    i1.salary,
    s2.id,
    s2.name,
    i2.salary
FROM students s1
INNER JOIN friends f
    ON s1.id = f.id
INNER JOIN students s2
    ON f.friend_id = s2.id
INNER JOIN incomes i1
    ON s1.id = i1.id
INNER JOIN incomes i2
    ON s2.id = i2.id;

Upvotes: 1

Related Questions