CODEWITHSUNDEEP
sqlpostgresqljoin
Marcus
Marcus

Reputation: 25

SQL find elements A not containing elements B and containing elements B with specific status

I have boxes, and items that can be contained in the boxes. Both boxes and items have a status. Boxes statuses are for example packed, unpacked, sent, received etc - an integer. Items statuses are scanned, not scanned and unknown (null) - a Boolean.

Or as tables:

So, a box can have many items, but an item can only be contained in one box.

As a start, I want to be able to find all boxes with status = 0 that contains either no items at all or only items with status false and/or null. In the end, I want to change the status of all the matching boxes to 1.

Sample data and expected data:

boxes [pk] int id, int status, character(20) test_text

0;0;"(no items)"
1;0;"(items with false)"
2;0;"(items with null)"
3;0;"(items with null and false)"
4;0;"(items with true)"
5;0;"(items with true and false)"
6;0;"(items with true and null)"
7;0;"(items with true and false and null)"
8;1;"(no items)"
9;1;"(items with false)"
10;1;"(items with null)"
11;1;"(items with true)"

items [pk] int id, int box_id, boolean status

0;1;false
1;2;null
2;3;false
3;3;null
4;4;true
5;5;true
6;5;false
7;6;true
8;6;null
9;7;true
10;7;false
11;7;null
12;9;false
13;10;null
14;11;true

expected - boxes [pk] int id, int status, character(20) test_text

0;0;"(no items)"
1;0;"(items with false)"
2;0;"(items with null)"
3;0;"(items with null and false)"

I can't quite figure out how to write the proper code to do this. I'm using postgresql.

Any help is appreciated!

Upvotes: 0

Views: 169

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

all boxes with status = 0

where boxes.status = 0

either no items at all or only items with status false and/or null.

i.e. boxes that don't contain any item with status true

where boxes.id not in (select box_id from items where status = true)

The complete query:

select *
from boxes
where status = 0
and id not in (select box_id from items where status = true);

(The NOT IN clause can be replaced by a NOT EXISTS clause, if you prefer this. I prefer NOT IN for its simplicity.)

Upvotes: 1

Ivan Burlutskiy
Ivan Burlutskiy

Reputation: 1623

Something like this: 

select bb.id 
from boxes bb
    left join items ii on ii.box_id = bb.id
where bb.status = 0
group by bb.id
having sum(case when ii.status = true then 1 else 0 end) = 0

Upvotes: 2

Related Questions