title: find three objects in a relationship with fewer joins
body: <p>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.</p>
  
  <p>I have the following tables: </p>
  
  <p><strong><code>Highschooler ( ID, name, grade )</code></strong></p>
  
  <p><strong><code>Friend ( ID1, ID2 )</code></strong> ID1 is a mutual friend of ID2 => if <code>[ID1, ID2]</code> exists within <code>Friend</code>, then so must <code>[ID2, ID1]</code></p>
  
  <p><strong><code>Likes ( ID1, ID2 )</code></strong> ID1 is attracted to ID2</p>
  
  <p>The question was: </p>
  
  <blockquote>
    <p>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. </p>
  </blockquote>
  
  <p>And my solution:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I feel like the <code>Highschooler H1, Highschooler H2, Highschooler H3</code> statement is my 'brute-forcing'. </p>
  
  <p>The <code>SELECT</code> statement within the first <code>FROM</code> correctly finds the <code>[A.ID, B.ID, C.ID]</code> trios whilst the rest of the statement just fetches the names and grades responding to those IDs.</p>
  
  <p>Is there a better way to do this fetching than the 4-way join I have done?</p>