SQL Join only if all records match

Question

I have 3 tables:

• CP_carthead (idOrder)
• CP_cartrows (idOrder, idCartRow)
• CP_shipping (idCartRow, idShipping, dateShipped)

There can be several idCartRows in idOrder.

I want to get all orders where all of its idCartRows exist in CP_shipping. It seems to be easy, but I have not found much on the Internet.

Here is my request now:

SELECT s.idOrder , s.LatestDateShipped FROM CP_carthead o LEFT OUTER JOIN ( SELECT MAX(s.dateShipped) [LatestDateShipped] , r.idOrder FROM CP_shipping s LEFT OUTER JOIN CP_cartrows r ON s.idCartRow = r.idCartRow GROUP BY r.idOrder ) s ON o.idOrder = s.idOrder 

Answer 1

Your query returns strings from "s", not orders. Based on your question, I came up with this query:

 select o.* from CP_Carthead o where o.orderId in (select cr.idOrder from cp_cartrows cr left outer join cp_shipping s on cr.idCartRow = s.IdCartrow group by cr.idOrder having count(s.idCartRow) = COUNT(*) ) 

The subquery in the in operator receives orders, all of whose charters are in shipment.