CODEWITHSUNDEEP
sqldatabasesqlite
Minos
Minos

Reputation: 35

Is it possible to rewrite this SQL statement using a join instead of subquery?

I have three tables:

animals:

| animal_id | animal_label |
|-----------|--------------|
| 1         | Elephant     |
| 2         | Fish         |
| 3         | Monkey       |
| 4         | Snake        |

fruits:

| fruid_id | fruit_label |
|----------|-------------|
| 1        | Apples      |
| 2        | Oranges     |
| 3        | Peanuts     |
| 4        | Bananas     |

likes:

| animal_id | fruit_id |
|-----------|----------|
| 1         | 1        |
| 2         | 2        |
| 3         | 3        |
| 3         | 4        |
| 4         | 1        |

Each animal can like multiple fruits and each fruit can be liked by multiple animals, so I have a many-to-many relationship between the animals and fruits. This relationship is described using the third table called likes.

I am trying to determine which animals don't like bananas. In order to do so, I wrote this statement:

SELECT animal_id, animal_label
FROM animals
WHERE animal_id NOT IN (
    SELECT animal_id
    FROM likes
    WHERE fruit_id = 4)

That successfully returns the elephant, fish and snake, but not the monkey, because the monkey likes bananas.

However, I was wondering if this statement could be rewritten using a join. This is what I tried so far:

SELECT animal_id, animal_label
FROM animals
LEFT OUTER JOIN likes ON animals.animal_id = likes.animal_id
WHERE fruit_id <> 4 OR fruit_id IS NULL

This time, all four animals are returned. The issue here is that because the monkey also likes peanuts, this statement will return it even though it likes bananas. Is there any way to solve this that would be better than my solution using the subquery?

Upvotes: 0

Views: 45

Answers (1)

juergen d
juergen d

Reputation: 204904

You can group by the animal and take only those having zero times fruit_id = 4

SELECT animal_id, animal_label
FROM animals
LEFT OUTER JOIN likes ON animals.animal_id = likes.animal_id
GROUP BY animal_id, animal_label
HAVING sum(fruit_id = 4) = 0

Upvotes: 2

Related Questions