Customers who likes banana but not apple - Improve Query-CodePudding

What is the better way to get the same output?

I only want those customers who likes 'Banana' but not 'Apple'

Example:

101, 102 and 108 likes both banana and apple so I don't want

105 likes apple so I don't want

106 doesn't like banana so I don't want

EXPECTED RESULT SHOULD BE 103,104,

DECLARE @SampleTable TABLE
    (
        CustomerNumber INT,
        Likes VARCHAR(20),
        Random VARCHAR(5)
    );

INSERT INTO @SampleTable (CustomerNumber, Likes, Random)
VALUES 
 (101, 'Banana', 'A'),
 (101, 'Orange', 'A'),
 (101, 'Melon', '1'),
 (101, 'Apple', 's'),
 (102, 'Banana', 'f'),
 (102, 'Apple', 'g'),
 (103, 'Banana', 'h'),
 (103, 'Melon', 'j'),
 (104, 'Banana', 'A'),
 (104, 'Mango', '1'),
 (105, 'Apple', 's'),
 (106, 'Grapes', 'f'),
 (107, 'Apple', 'g'),
 (108, 'Banana', 'h'),
 (108, 'Apple', 'j');

SELECT * FROM @SampleTable;

SELECT * FROM @SampleTable
WHERE CustomerNumber NOT IN
(SELECT CustomerNumber FROM @SampleTable WHERE Likes = 'Apple')
AND CustomerNumber IN
(SELECT CustomerNumber FROM @SampleTable WHERE Likes = 'Banana')

CodePudding user response：

Another way, but not necessarily better

with nuts as (
 select *,
  Max(case when likes='apple'  then 1 else 0 end) over(partition by customernumber) apple,
  Max(case when likes='banana' then 1 else 0 end) over(partition by customernumber) banana
 from SampleTable
)
select customernumber, likes, random
from nuts
where banana=1 and apple=0

CodePudding user response：

You can use one condition directly thus removing one of sub-queries:

SELECT * 
FROM @SampleTable
WHERE Likes = 'Banana'
AND CustomerNumber NOT IN
(SELECT CustomerNumber FROM @SampleTable WHERE Likes = 'Apple')

Or do the same with EXISTS and correlated sub-query:

SELECT * 
FROM SampleTable t1
WHERE Likes = 'Banana'
AND NOT EXISTS 
(SELECT 1 
 FROM SampleTable t2 WHERE t2.Likes = 'Apple' 
                       AND t1.CustomerNumber=t2.CustomerNumber);

DEMO (based on MySQL)