Re: SQL: how to join header and detail records

On 03 Apr 2013 11:53, Birgitta Hauser wrote:

On 03 Apr 2013 09:52, Stone, Joel wrote:

I have an order header and detail file with one to many.
<<SNIP>>
Should I be grouping the detail PRIOR to the join, so there is only
ONE header value?

But it seems like the SQL stmt is SO much simpler if I do the join
first.

I'd use the following query:

With
TotDet as
(Select OrderNo, Sum(DetQty) DetQTY
From OrderDet
Group By OrderNo
)
Select Coalesce(H.OrderNo, D.OrderNo)
, HdrQty, DetQty
From OrderHdr h
full outer join TotDet d
on h.OrderNo = d.OrderNo
Where HdrQty <> DetQty
or HdrQty is NULL
or DetQty is NULL
;

That is thorough, but is either likely to be or at least should be excessive, if one is to believe the one-to-many claim in\by the OP.? Though I suppose given the question was even asked, the vernacular of a layperson might best be presumed over that of a database person.

While I am unsure of what the OP meant by not having the aggregate query [appear] *prior* to the JOIN, my inference was that a CTE was what was alluded and thus was desired to be eliminated.?

Of course since the OP offered none of the DDL, their original query they wanted to change, or even sample data, all of that would just be speculation.