samedi 5 septembre 2015

SQL - Map table of foreign keys to value from another table's tuple using the key

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