StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is harder than it looks, because it requires preparing sets sequentially. But, there are a few ways to solve this one. Here's what quickly comes to mind: </p> <p>First, find the friend-of-friend for everybody by grade producing something like:</p> <p>[ID], [FoF ID], [Grade of FoF]</p> <p>You really don't need [FoF ID], but it might help when debugging. </p> <p>Then, as a second-order operation, you'll need to produce a list of [ID]s where [Grade of FoF] is equal to both the MAX() and MIN(): </p> <p>SELECT [ID], MAX(Grade of FoF) as A, MIN(Grade of FoF) as B FROM [the above] WHERE A=B</p> <p><strong>UPDATE</strong>: I realized that I should also add that in the final qry: A=B and A=Grade. Then this solution works. Keep in mind: it only answers the question "Find names and grades of students who only have friends in the same grade." and it assumes friendship is one-directional. (Sorry, I had to leave something undone.)</p> <p>For those that need to see some SQL, here you are. It's written for MS Access, but easily ported (start by removing the "()" in the inner-most query) to MySQL, PGSQL, or Oracle. Better still, no procedural extensions and no temp tables. </p> <p>SELECT name FROM ( SELECT ID ,name ,grade ,min( friend_grade) as min_friend_grade ,max( friend_grade) as max_friend_grade FROM ( SELECT hs1.ID ,hs1.name ,hs1.grade ,l.ID2 as friend_id ,hs2.name as friend_name ,hs2.grade as friend_grade</p> <p>FROM ( highschooler hs1 INNER JOIN likes l ON (hs1.ID = l.ID1) ) INNER JOIN highschooler hs2 ON (l.ID2 = hs2.ID)</p> <p>)FoF</p> <p>GROUP BY ID ,name ,grade</p> <p>)FoF_max_min</p> <p>WHERE grade=min_friend_grade AND min_friend_grade=max_friend_grade</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload