CODEWITHSUNDEEP
sqlsql-server
Baldie47
Baldie47

Reputation: 1264

Select rows when related field does not include any relation

I need to return all clients from orders table only when they haven't made any order that contains element in ('type 1', 'type 2').

that means the list should only bring the clients that never made an order containing type1 or type2. (an orderitem can have several elements, and a client can have several orders)

I'm trying with the following

Select client
From Orders o 
left join orderItems oi On oi.id = o.id
Where Not Exists
( 
    Select * from orderItems 
    Where oi.element in ('type 1', 'type 2')
)

and below an example table

+----+----------+
| id | client   |
+----+----------+
| 1  | client 1 |
+----+----------+
| 2  | client 2 |
+----+----------+
| 3  | client 3 |
+----+----------+
| 4  | client 4 |
+----+----------+

+----+---------+
| id | element |
+----+---------+
| 1  | type 3  |
+----+---------+
| 1  | type 3  |
+----+---------+
| 2  | type 1  |
+----+---------+
| 2  | type 3  |
+----+---------+
| 2  | type 3  |
+----+---------+
| 3  | type 2  |
+----+---------+
| 3  | type 3  |
+----+---------+
| 4  | type 3  |
+----+---------+

in this case, the result should be only client 1 and client 4 since only them don't have element type 1 or 2.

I also tried:

Select client
From Orders o 
left join orderItems oi On oi.id = o.id
group by client
having count(case when oi.element in ('type 1', 'type 2') then 1 end) = 0

Upvotes: 1

Views: 55

Answers (2)

prem
prem

Reputation: 323

Select client
From Orders
Where id NOT IN (SELECT Distinct id From orderItems 
             Where element in ('type 1', 'type 2'));

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

Using NOT EXISTS and correlated subquery:

Select client
From Orders o 
Where Not Exists(Select * from orderItems oi 
                 Where oi.id = o.id AND oi.element in ('type 1', 'type 2'));

db<>fiddle demo

Upvotes: 1

Related Questions