I could not think of an appropriate title, suggestions appreciated.
I'm doing an SQL excerise and determined a solution however I feel like the joining of 4 tables in my solution is brute force way to do what I did.
I have the following tables:
Highschooler ( ID, name, grade )
Friend ( ID1, ID2 ) ID1 is a mutual friend of ID2 => if [ID1, ID2] exists within Friend, then so must [ID2, ID1]
Likes ( ID1, ID2 ) ID1 is attracted to ID2
The question was:
For each student A who likes a student B where the two are not friends, find if they have a friend C in common (who can introduce them!). For all such trios, return the name and grade of A, B, and C.
And my solution:
Select H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade
FROM Highschooler H1, Highschooler H2, Highschooler H3,
-- Following table contains each trio of A, B, C
(Select L.ID1 as ID1, L.ID2 as ID2, F.ID2 as ID3
FROM (SELECT * FROM Likes EXCEPT SELECT * FROM Friend) as L, Friend as F
WHERE L.ID1 = F.ID1 AND -- Found a potential friend C of A
F.ID2 in (SELECT ID2 FROM Friend WHERE Friend.ID1 = L.ID2)) -- Does potential C appear in list of B's friends?
WHERE H1.ID = ID1 and H2.ID = ID2 and H3.ID = ID3
I feel like the Highschooler H1, Highschooler H2, Highschooler H3 statement is my 'brute-forcing'.
The SELECT statement within the first FROM correctly finds the [A.ID, B.ID, C.ID] trios whilst the rest of the statement just fetches the names and grades responding to those IDs.
Is there a better way to do this fetching than the 4-way join I have done?
Aucun commentaire:
Enregistrer un commentaire