Sql Join Only If All Records Have A Match

I have 3 tables: CP_carthead (idOrder) CP_cartrows (idOrder, idCartRow) CP_shipping (idCartRow, idShipping, dateShipped) There can be multiple idCartRows per idOrder. I want to

Solution 1:

Your query is returning rows from "s" and not the 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 leftouterjoin
                         cp_shipping s
                         on cr.idCartRow = s.IdCartrow  
                    groupby cr.idOrder
                    havingcount(s.idCartRow) =COUNT(*)
                   )

The subquery in the in statement is getting orders all of whose cartrows are in shipping.