I have two tables, with the below layouts
persons:
person_id,
first_name,
last_name,
gender,
birth_date,
street,
town,
county
likes:
person_id,
food
I'm attempting to "List the names of those who do not like beer" from the above two tables, using just JOINS (I'm not allowed to use Subqueries).
The below code works fine if I change the WHERE clause to equals beer. It results in the two people who like beer.
But when I attempt to change it to those who don't like beer (it's not a real table, everybody likes beer ;)), the issue is it removes the exact row where the food preference is equal to beer. But the person who has other preferences like "pizza" but likes beer is still included.
The query is removing the relevant row for beer but not all the other rows related to this person.
How can I fix this without using a subquery?
SELECT
p1.first_name, p1.last_name, l1.food, l1.person_id
FROM
persons AS p1
JOIN likes AS l1
ON p1.person_id = l1.person_id
WHERE l1.food <> "Beer"
For example, Aoife likes beer but also likes pizza. I want Aoife to be removed fully from the query results as she likes beer.
List the names of those who do not like beer
You can use the LEFT JOIN antipattern:
SELECT p1.first_name, p1.last_name
FROM persons AS p1
LEFT JOIN likes AS l1 ON p1.person_id = l1.person_id AND l1.food = 'Beer'
WHERE l1.person_id IS NULL
This attempts to join the each person with a record in likes with food = 'Beer', and then filters on non-matching records only.
Another option is to use a not exists condition with a correlated subquery:
SELECT p1.first_name, p1.last_name
FROM persons AS p1
WHERE NOT EXISTS (
SELECT 1
FROM likes l1
WHERE p1.person_id = l1.person_id AND l1.food = 'Beer'
)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments