looking for values from another table where they do not exist in a given group

user_14493753 0
Viewed 22

I have two tables:

SHOPPING

date id_customer id_shop id_fruit
28.03.2018 7423 123 1
13.02.2019 8408 354 1
28.03.2019 7767 123 9
13.02.2020 8543 472 7
28.03.2020 8640 346 9
13.02.2021 7375 323 9
28.03.2021 7474 323 8
13.02.2022 7476 499 1
28.03.2022 7299 123 4
13.02.2023 8879 281 2
28.03.2023 8353 452 1
13.02.2024 8608 499 6
28.03.2024 8867 318 1
13.02.2025 7997 499 6
28.03.2025 7715 499 4
13.02.2026 7673 441 7

FRUITS

id_fruit name
1 apple
2 pear
3 grape
4 banana
5 plum
6 melon
7 watermelon
8 orange
9 pineapple

I would like to find fruits that have never been bought in a specific id_shop

I tried with this:

SELECT
    s.idshop,
    s.id_fruit ,
    f.name
FROM
    shopping s
    LEFT JOIN fruit f ON f.id_fruit = s.id_fruit 
WHERE NOT EXISTS (
    SELECT *
    FROM
    fruit f1
    WHERE f1.id_fruit = s.id_fruit 
    )

but it does not work...

oracle sql
1 Answers

Yes, you need an OUTER JOIN, but that should be RIGHT JOIN along with NULL values picked from shopping table after join applied, considering your current query such as

SELECT f.*
  FROM shopping s
 RIGHT JOIN fruit f
    ON f.id_fruit = s.id_fruit
 WHERE s.id_fruit IS NULL

Demo

user_58413060
Related