3

I have three tables I need to join together. Specifically table 1 needs to join to table 2 and table 2 joins to table 3. I need to return values from table 1 where all instances of the values selected in table 1 in table 2 have a field of some value and then further selected by my join on table 3. It may be easier to present the example.

Table 1

Order Status
001 A
002 A
003 B
004 B
005 A

Table 2

Box Status Order Shipment
1 X 001 1
2 X 001 2
3 X 002 1
4 X 003 2
5 X 003 2
6 Y 004 1
7 X 004 2
8 X 004 1
9 Y 005 1

Table 3

Shipment Status
1 C
2 A

I need to select all orders from table 1 that are in status 'A' AND are tied to boxes in table 2 where all of the boxes for the order are in status 'X' and are tied to a shipment in table 3 that is in status 'C'.

My end result should return the following:

Order
002

I have the following but it is not 100% accurate as Table2.Shipment can be a blank value. My real issue is difficulty in finding orders from table1 where all the boxes for that order in table 2 are in the same status.

SELECT order
FROM table1
JOIN table2 ON table1.order = table2.order
WHERE table2.order NOT IN
(SELECT table2.order FROM table2
JOIN table3 ON table2.shipment=table3.shipment
WHERE table3.status = 'A')
AND table2.order IN
(SELECT table2.order FROM table2
JOIN table3 ON table2.order = table3.order
WHERE table3.status = 'C') 
AND table1.status = 'A'
AND table2.status = 'X'
GMB
  • 216,147
  • 25
  • 84
  • 135
MethodManican
  • 33
  • 2

1 Answers1

1

You can use aggregation, and filter on t2 and t3 with a having clause:

select t1.orderid
from table1 t1
inner join table2 t2 on t2.orderid  = t1.orderid
inner join table3 t3 on t3.shipment = t2.shipment
where t1.status = 'A'
group by t1.orderid
having max(case when t2.status <> 'X' then 1 else 0 end) = 0 
   and max(case when t3.status <> 'C' then 1 else 0 end) = 0
GMB
  • 216,147
  • 25
  • 84
  • 135