Incorrect result from correlated subquery

Question

My task is: Find names and grades of students who only have friends in the same grade.

My tables:

HIGHSCHOOLER

ID  name    grade
1510    Jordan  9
1689    Gabriel 9
1381    Tiffany 9
1709    Cassandra   9
1101    Haley   10
1782    Andrew  10
1468    Kris    10
1641    Brittany    10
1247    Alexis  11
1316    Austin  11
1911    Gabriel 11
1501    Jessica 11
1304    Jordan  12
1025    John    12
1934    Kyle    12
1661    Logan   12

FRIEND

ID1 ID2
1510    1381
1510    1689
1689    1709
1381    1247
1709    1247
1689    1782
1782    1468
1782    1316
1782    1304
1468    1101
1468    1641
1101    1641
1247    1911
1247    1501
1911    1501
1501    1934
1316    1934
1934    1304
1304    1661
1661    1025
1381    1510
1689    1510
1709    1689
1247    1381
1247    1709
1782    1689
1468    1782
1316    1782
1304    1782
1101    1468
1641    1468
1641    1101
1911    1247
1501    1247
1501    1911
1934    1501
1934    1316
1304    1934
1661    1304
1025    1661

My query:

select name, grade from highschooler
where id in
(  select distinct id1 
   from friend out
   where not exists
     (select id1
      from friend
      where    
          (select grade from highschooler where id = out.id1) <>
          (select grade from highschooler where id = out.id2)
     )
)

My result:

Jordan  9
Gabriel 9
Tiffany 9
Cassandra   9
Haley   10
Andrew  10
Kris    10
Brittany    10
Alexis  11
Gabriel 11
Jessica 11
Jordan  12
John    12
Kyle    12
Logan   12

Expected Query Result:

Jordan  9
Brittany    10
Haley   10
Kris    10
Gabriel 11
John    12
Logan   12

I scrutinize my code with Tiffany:

1381    Tiffany 9   1247    Alexis  11
1381    Tiffany 9   1510    Jordan  9

Something is wrong with my correlated subquery. Could you give me a hint on what is wrong?

sgeddes · Answer 1 · 2013-02-03T05:15:17.837

0

To get only students who have friends in their grade and have no other friends, try this:

SELECT DISTINCT H.Id, H.Name, H.Grade
FROM HighSchooler H
  JOIN Friend F ON H.Id = F.ID1
  JOIN HighSchooler H2 ON F.ID2 = H2.ID AND H2.Grade = H.Grade
WHERE NOT EXISTS 
 (SELECT *
  FROM HighSchooler H3
  JOIN Friend F2 ON H3.Id = F2.Id1
  JOIN HighSchooler H4 ON F2.Id2 = H4.Id
  WHERE H4.Grade <> H.Grade AND H3.Id = H.Id)

And here is the SQL Fiddle.

Good luck.

edited Feb 03 '13 at 05:15

answered Feb 03 '13 at 04:58

sgeddes

62,311
6
61
83

Thank you. But unfortunately this is not what I need. This will not return the correct result. – Pepperwork Feb 03 '13 at 05:03
Please see edits -- to understand NOT EXISTS -- change WHERE H.ID NOT IN to WHERE NOT EXISTS and that should pretty much do the same thing. Let me create a fiddle for testing... – sgeddes Feb 03 '13 at 05:06
No, your query returns all the highschoolers. Not working unfortunately. – Pepperwork Feb 03 '13 at 05:08
Ok -- see edits -- and here is the Fiddle: http://sqlfiddle.com/#!3/a1c84/6 – sgeddes Feb 03 '13 at 05:16
Working. But I can't catch what is wrong with my variant. – Pepperwork Feb 03 '13 at 05:24
Yes, you have given me an example of a working query. But maybe yout could explain where I made a mistake. – Pepperwork Feb 03 '13 at 05:30

score 0 · Accepted Answer · answered Feb 03 '13 at 05:34

I'd use TDQD — Test-Driven Query Design — to solve this.

Determine the grades of the friends of a particular highschooler

SELECT f.id1 AS h1_id, h1.grade AS h1_grade, h2.grade AS h2_grade
  FROM Friends AS f
  JOIN Highschoolers AS h1 ON f.id1 = h1.id
  JOIN Highschoolers AS h2 ON f.id2 = h2.id

This lists, for each friend, the highschooler whose friend it is, the highschooler's grade and the friend's grade.

Generate a list of students who have friends not in their own grade

SELECT DISTINCT h1_id
  FROM (SELECT f.id1 AS h1_id, h1.grade AS h1_grade, h2.grade AS h2_grade
          FROM Friends AS f
          JOIN Highschoolers AS h1 ON f.id1 = h1.id
          JOIN Highschoolers AS h2 ON f.id2 = h2.id
       )
 WHERE h1_grade != h2_grade;

Generate a list of students who have at least one friend and have no friends not in their grade

SELECT h.id, h.name, h.grade
  FROM Highschooolers AS h
  JOIN Friends AS f ON h.id = f.id1
 WHERE h.id NOT IN
       (SELECT DISTINCT h1_id
          FROM (SELECT f.id1 AS h1_id, h1.grade AS h1_grade, h2.grade AS h2_grade
                  FROM Friends AS f
                  JOIN Highschoolers AS h1 ON f.id1 = h1.id
                  JOIN Highschoolers AS h2 ON f.id2 = h2.id
               )
         WHERE h1_grade != h2_grade
       )

This will select highschoolers who do appear in the id1 column of the Friends table; they have at least one friend listed, and every one of their friends is in the same grade.

Generate a list of students who have no friends not in their grade

SELECT h.id, h.name, h.grade
  FROM Highschooolers AS h
 WHERE h.id NOT IN
       (SELECT DISTINCT h1_id
          FROM (SELECT f.id1 AS h1_id, h1.grade AS h1_grade, h2.grade AS h2_grade
                  FROM Friends AS f
                  JOIN Highschoolers AS h1 ON f.id1 = h1.id
                  JOIN Highschoolers AS h2 ON f.id2 = h2.id
               )
         WHERE h1_grade != h2_grade
       )

This will select highschoolers who do not appear in the id1 column of the Friends table; they have no friends listed (presumably data not entered rather than being very solitary) so not one of their friends is in a different grade.

Note that all these queries assume that it is not necessarily the case that Student B lists Student A as a friend just because Student A lists Student B as a friend. That is, if the entry (1234, 2345) appears in the friends table (stating that student 1234 thinks 2345 is a friend), it does not follow automatically that 2345 considers 1234 a friend; there would have to be an explicit converse entry (2345, 1234) to indicate the reciprocal relationship. The queries get more complex if the friendships are automatically reciprocated.

score 0 · Answer 3 · answered Feb 03 '13 at 05:35

This works:

select "name", "grade" from highschooler a
where not exists
(
  select 1 from (
(select id1, id2, count(distinct "grade") from (
SELECT *
FROM friend f, highschooler h
where f.id1 = h.id
or f.id2 = h.id
order by id1,id2)
group by id1, id2
having count(distinct "grade") > 1))
  where id1 = a.id or id2 = a.id)
order by "grade", "name"

SQLFIDDLE for the above query

score 0 · Answer 4 · answered Feb 04 '13 at 12:44

This Works For Sure, I have Tried Running it...

Select distinct h1.name,h1.grade  
From Highschooler h1,Highschooler h2,Friend
Where h1.ID = Friend.ID1
AND h2.ID = Friend.ID2
AND h1.grade = h2.grade

EXCEPT
Select distinct h1.name,h1.grade
From Highschooler h1,Highschooler h2,Friend
Where h1.ID = Friend.ID1
AND h2.ID = Friend.ID2
AND h1.grade <> h2.grade
order by h1.grade,h1.name