I am taking Stanford's Introduction to Databases Self-Paced online course. I have gone through the videos in the SQL mini-course, and I am having trouble completing the exercises.
The following is the question from the SQL Social-Networking Query Exercises, Question 5:
For every situation where student A likes student B, but we have no information about whom B likes (that is, B does not appear as an ID1 in the Likes table), return A and B's names and grades.
The database can be found here or download the schema + data.
My answer to this question is as follows:
SELECT H1.name, H1.grade, H2.name, H2.grade
FROM (SELECT * FROM Likes L1
WHERE L1.ID2 NOT IN (SELECT L2.ID1 FROM Likes L2)) F INNER JOIN
Highschooler H1 ON F.ID1 = H1.ID INNER JOIN Highschooler H2 ON F.ID2 =
H2.ID;
I want to avoid using the WHERE L1.ID2 NOT IN... part of the query.
In particular, I'd like to use two result sets.
The first being the entire Likes relation: SELECT * FROM Likes L1;
Then I'd like to take the results from the following query: SELECT L2.ID FROM Likes L2
And then someone filter the first result set such that it only includes the tuples in result set 1 if L1.ID2 is not in result set 2.
I feel like there's a join or maybe set operator that can do this?