SQL query show customers who bought apples, but not potatoes-CodePudding

Not sure how to explain this..

I have a similar table, but i have simplified it with the following:

I have a table of goods shipped to different cusotmers. Some have bought apples only, others have bought apples and potates.

I want an SQL query to return only customers where "To be billed" = Yes AND the customer hasnt bought any vegetables.

So for example if the table looks like this:

Item	Name	Group	To_be_billed	CustomerNo.
2000	Apple	Fruit	Yes	1
2000	Apple	Fruit	No	2
2000	Apple	Fruit	No	3
2000	Apple	Fruit	Yes	4
2000	Apple	Fruit	Yes	5
4000	Potato	Vegetable	No	2
4000	Potato	Vegetable	No	4

I want the query to return:

Item	Name	Group	To_be_billed	CustomerNo.
2000	Apple	Fruit	Yes	1
2000	Apple	Fruit	Yes	5

The reason 4 has bought apples, and is to be billed, but the customer also bought Potatoes, so is to be ignored...

CodePudding user response：

You can create a CTE to check for CustomerNo.s that you need to ignore, and then use not exists:

with bought_veg as
(
    select "CustomerNo."
    from tbl
    where tbl."Group" like 'Vegetable'
)
select tbl.*
from tbl
where not exists (select 1 from bought_veg where tbl."CustomerNo." = bought_veg."CustomerNo.")
    and tbl.To_be_billed = 'Yes'

Example without CTE:

select tbl.*
from tbl
where not exists (select "CustomerNo." from tbl t2 where tbl.[CustomerNo.] = t2.[CustomerNo.] and "Group" like 'Vegetable')
    and tbl.To_be_billed = 'Yes'