I have a table of products which lists all the products held within our branches. This includes whether a product is a PackingLine one. A product either is or is not a PackingLine, it doesn’t change per branch, however not all branches have every packing line. I need to find those branches and products. A simplified version would be
| Branch | FullDesc | PackingLine |
|---|---|---|
| 1 | Prod A | 1 |
| 1 | Prod B | 0 |
| 2 | Prod B | 0 |
| 2 | Prod C | 0 |
| 3 | Prod D | 1 |
I need to select all rows where a branch doesn’t have a item that is marked as a packing line inside another branch. i.e. in the example above I would expect
| Branch | FullDesc |
|---|---|
| 1 | Prod D |
| 2 | Prod A |
| 2 | Prod D |
| 3 | Prod A |
As Prod A and D are packing lines and Branch 2 has neither and Branch 1 and 3 has one the other doesn’t
My original thought was to create a cte to get a distinct list of Packing Lines (excl. branch) and check for Non-Exists,
WITH cte AS (
SELECT FullDesc FROM products.Product WHERE PackingRobotLine = 1
)
SELECT Branch, FullDesc FROM products.Product WHERE NOT EXISTS
(SELECT FullDesc FROM cte
WHERE products.Product.FullDesc = cte.FullDesc)
but this of course returns all rows not in the cte including ones that are not PackingLine ones.
What would be the correct way to obtain the required rows.