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?
1 Answer 1
Formatting
Using indentation and line breaks would make your code easier to read. There is no real "standard" so go with what you feel is clearest. I usually go with something that resembles C indentation a bit. Others use a more Java-like indentation. Doesn't matter, as long as it makes it more readable.
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;
F and other aliases
It's good practice for aliases to get meaningful names. Making the code shorter is all well and good, and in your case doing a self-JOIN they are necessary; but, your subquery being aliased as F doesn't say much about what it represents.
MySQL limitations
There are several things you could do in other RDBMS that MySQL doesn't allow. You could have made a Common Table Expression, declared a Table Variable, and even used EXCEPT; except, none of those are supported by MySQL. I'm afraid, given the tool, this is about as good as it will get. Perhaps someone else will know of a better way. Only other thing I could think of is using a temp table, but that's totally overkill for something simple like this.