Using the following table (customer_table):

How do I select groups of customer references that don't use a download as a service?

If I do: SELECT customer_ref FROM customer_table WHERE service <> download

It will return customer_ref PI-724 and PI-729 which do have a service that isn't download but I don't want to return them because under the same customer_ref they do have a service that is download - if that makes sense.

Under my parameters I want to return the following customer_ref:

CodePudding user response：

Jointure way

SELECT c1.customer_ref  
FROM customer_table  c1
LEFT JOIN customer_table  c2 ON c1.customer_ref=c2.customer_ref AND c2.service='download'
WHERE c2.customer_ref IS NULL

CodePudding user response：

You can aggregate and using bool_and() in the having clause:

select customer_ref
from the_table
group by customer_ref
having bool_and(service <> 'download');

bool_and(service <> 'download') will only return true if all services of the customer_ref are not equal to download

CodePudding user response：

Set operation can help you

select customer_ref from customer_table
EXCEPT /* = MINUS */
select customer_ref from customer_table 
where service = 'download'

It is as easy as you would formulate it: all customers except those that used download.

Note also that the set operations return distinct key, so you do not nead to used distinct or some group by

CodePudding user response：

try like below using aggregation

select customer_ref from
group by customer_ref
having sum(case when service='download' then 1 else 0 end)=0