CODEWITHSUNDEEP
sqloracle-database
Ekfa
Ekfa

Reputation: 115

SQL to find "clients" with a specific missing attribute

Is there a way to use a SQL statement on 1 table in which the result is the clients who do NOT have one specific attribute?

The table exists of multiple columns. One of them is Clientand another one is Product. One client can have several different records with different product-values. Every client should at least have one specific product (for example X), next to a lot of different other values of product he can have. I would like to use a statement which returns all clients who don't have product X.

Upvotes: 0

Views: 165

Answers (5)

Popeye
Popeye

Reputation: 35920

There are several ways:

  • Using NOT EXISTS as following:

    SELECT client
  FROM yourTable T
 WHERE NOT EXISTS 
  (SELECT 1 FROM yourTable TIN 
    WHERE TIN.product = 'product X'
      AND T.CLIENT = TIN.CLIENT
  );

  • Using NOT IN 

    SELECT client
  FROM yourTable T
 WHERE client not in 
  (SELECT tin.client FROM yourTable TIN 
    WHERE TIN.product = 'product X'
  );

  • Using group by, as shown in the other answers

    select client
  from yourTable
group by client
HAVING COUNT(CASE WHEN product = 'product X' THEN 1 END) = 0;

Upvotes: 1

matek997
matek997

Reputation: 351

Use subquery to get all records which have X and then in the main query get all records which are not in this subset:

SELECT DISTINCT table_name.client FROM table_name WHERE table_name.client NOT IN (SELECT DISTINCT table_name.client FROM table_name WHERE table_name.product = 'X')

Upvotes: 0

Someone from Austria
Someone from Austria

Reputation: 1

SELECT [Clients] FROM [tablename] WHERE Product != 'X'

Upvotes: 0

Stoycho Dimitrov
Stoycho Dimitrov

Reputation: 5

Try it with NOT IN. 

SELECT * 
 FROM table1
WHERE product NOT IN ('X')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

Aggregation is one simple option:

SELECT client
FROM yourTable
GROUP BY client
HAVING COUNT(CASE WHEN product = 'product X' THEN 1 END) = 0;

This works by keeping a count, for each client, of each record which matches product X. Assuming a client never has this product, the count would be zero.

Upvotes: 0

Related Questions