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

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...

CodePudding user response：

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