How to write CASE statement with condition on values from Sub-query in SQLite

Question

The Highschooler table has data in the below format:

Highschooler ( ID, name, grade )

English: There is a high school student with unique ID and a given first name in a certain grade.

The sample data in the table is:

ID       NAME      GRADE
1510    Jordan      9
1689    Gabriel     9
1381    Tiffany     9
1709    Cassandra   9
1101    Haley       10
1782    Andrew      10
1468    Kris        10

The likes table has data in the below format:

Likes ( ID1, ID2 )

English: The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also present.

The sample data in the Likes table is as follows:

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

Now the question I have is:

For every student who likes someone 2 or more grades younger than themselves, return that student's name and grade, and the name and grade of the student they like.

To achieve this I have written the below query where I'm able to get the data of the student, liked_student and both their grades. What I have to do is subtract the grades of Liked_student and Student himself if the grades are >= 2 then I output the details of students. This checking for grades is what I'm trying to do in the CASE statement which is failing.

When I execute the inner query the sub-query is running with out any issue, meaning I'm able to fetch all the details of the student. The results of sub-query are as follows:

 DIFFERENCE STUDENT_GRADE STUDENT_NAME STUDENT_LIKED_GRADE STUDENT_LIKED_NAME

    0         9           Cassandra          9         Gabriel
    0         9           Gabriel            9         Cassandra
    1         10          Andrew             9         Cassandra
    2         12          John               10        Haley

It is only at the CASE statement it is giving out an error as

Error Code: 1109. Unknown table 'INTER1' in field list

    SELECT CASE WHEN INTER1.GRADE_DIFFERENCE >= 2 THEN 
        (SELECT INTER1.* FROM
        (SELECT (H1.grade-H2.grade) AS GRADE_DIFFERENCE, 
                 H1.grade AS STUDENT_GRADE, 
                 H1.name AS STUDENT_NAME, 
                 H2.grade AS STUDENT_LIKED_GRADE, 
                 H2.name AS STUDENT_LIKED_NAME
         FROM Highschooler H1
         INNER JOIN Likes L
         ON H1.ID = L.ID1
         INNER JOIN Highschooler H2 
         ON H2.ID = L.ID2) INTER1)
         END

How can I accomplish this? I have tried IF...THEN but couldn't make it work.

Kindly help.

Please provide some sample data and your desired output. (See [How to format SQL tables in a Stack Overflow post?](https://meta.stackexchange.com/q/96125) for how to add some.) — CL., Aug 02 '17 at 08:04
@CL. I have added the details as suggested. If you want to have any more details I would incorporate them. Thank you. — Sreedhar Danturthi, Aug 02 '17 at 09:16
Imagine that the output of the inner query is a table. How would you retrieve the rows where the `difference` column is at least two? — CL., Aug 02 '17 at 09:27
select STUDENT_GRADE, STUDENT_NAME, STUDENT_LIKED_GRADE, STUDENT_LIKED_NAME from TABLE where DIFFERENCE >=2 — Sreedhar Danturthi, Aug 02 '17 at 09:35
@CL. Thank you. I was just so engrossed to make it work with CASE/IF statements, that I could not think an alternative when the answer was so obvious and simple. — Sreedhar Danturthi, Aug 02 '17 at 09:50

score 1 · Answer 1 · answered Aug 02 '17 at 09:39

This gave the required answer:

SELECT INTER1.STUDENT_GRADE, 
       INTER1.STUDENT_NAME, 
       INTER1.STUDENT_LIKED_GRADE,
       INTER1.STUDENT_LIKED_NAME 
       FROM 
       (SELECT (H1.grade-H2.grade) AS GRADE_DIFFERENCE, 
                H1.grade AS STUDENT_GRADE, 
                H1.name AS STUDENT_NAME, 
                H2.grade AS STUDENT_LIKED_GRADE, 
                H2.name AS STUDENT_LIKED_NAME
        FROM Highschooler H1
        INNER JOIN Likes L
        ON H1.ID = L.ID1
        INNER JOIN Highschooler H2 
        ON H2.ID = L.ID2) INTER1
        WHERE INTER1.GRADE_DIFFERENCE >=2;

How to write CASE statement with condition on values from Sub-query in SQLite

1 Answers1