Re: SQL: how to join header and detail records

On 03 Apr 2013 11:20, rob@xxxxxxxxx wrote:

Ok, there's a bug with:

Select orderno, totqty, sum(dtlqty) as dtlsum
from orderheader
join orderdetail using (orderno)
group by orderno, totqty -- put totqty in group by to avoid MAX
having totqty <> sum(dtlqty)

ORDERNO TOTQTY DTLSUM
2 12 10
1 10 10

After you figure out that one, let me throw another loop at you.
Add orderno 3 into the header with a TOTQTY=12 and no detail
records.

That effect is no more a bug than my NewsReader treating everything following a newline\record with just two dashes and a space, within stream record data, being treated as an End-Of-Message [EOM]; thus my NewsReader not including that text in my replies, and the archives purging that text.

The above query should work fine [i.e. the HAVING clause is no longer treated as a comment] when using a row-based or stream-record-based input method instead of using a purely stream-based input method. The slash-asterisk method of commenting is much safer when embedded within a statement.

As to the second caveat, that is why I suggest [depending on the unstated DDL; i.e. no DDL was offered in\by the OP] that the JOIN should be either LEFT *or* INNER, though I did not clarify why. But my DDL made sure a FOREIGN KEY ensured LEFT was unnecessary for my given examples.