SQL Join only if all records have a match

Question

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 get all orders where all its idCartRows exist in CP_shipping. This seems like it should be simple, but I haven't found much on the web.

Here's my query 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

From looking at the query why not just use the sub query and drop the outer? And change the `left join` to `CP_cartrows` to an `inner join` — Magnus, Jan 03 '13 at 16:41
Does this answer your question? [SQL Select only rows where multiple relationships exist](https://stackoverflow.com/questions/14098243/sql-select-only-rows-where-multiple-relationships-exist) — philipxy, Apr 08 '20 at 06:57

score 3 · Accepted Answer · answered Jan 03 '13 at 16:30

3

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 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 statement is getting orders all of whose cartrows are in shipping.

answered Jan 03 '13 at 16:30

Gordon Linoff

1,242,037
58
646
786

He is selecting values only from the joined sub-query, yours only from `CP_Carthead` – Magnus Jan 03 '13 at 16:33
@Magnus . . . I know. I am answering the question ("I want to get orders . . . "), not replicating the query. – Gordon Linoff Jan 03 '13 at 16:36
This worked for me. My query now looks the same as was - I just added the where clause. Is there a better way to implement this method? – Rivka Jan 03 '13 at 17:22
@Rivka . . . An `in` with a subquery can be replaced by a `join`. However, the `in` is often the clearer way to express the query. – Gordon Linoff Jan 03 '13 at 17:25

SQL Join only if all records have a match

1 Answers1

Linked