Re: Please Help!! I'm going to JOIN a mental asylum!!
From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 14 Jun 2004 19:46:29 -0700
Message-ID: <18c7b3c2.0406141846.2cdb71f6_at_posting.google.com>
AND invh.invdate > '2004-05-01'
AND invd.item = crdd.item
Date: 14 Jun 2004 19:46:29 -0700
Message-ID: <18c7b3c2.0406141846.2cdb71f6_at_posting.google.com>
Your code is a bitch to read! Needless double quotes and uppercase, illegal characters in aliases. It looks as if you are printing an end user report in SQL.
Can I assume that:
invh = invoice headers invd = invoice details crdh = credit headers crdd = credit detail
Without DDL, all I can do is guess. The usual pattern is to build a header and its details together (invoices and credits?) then do the outer join. Let me pull off the prefixes (OE = Oxford English?) and clean it up a bit:
SELECT invd.item, invd.qtyshipped, invd.extinvmisc, invd.exticost,
invh.inv_nbr, invh.customer, crdh.crd_nbr, SUM(crdd.qtyreturn) AS total_quantity_returned, SUM(crdd.extcrdmisc) AS total_return, SUM(crdd.extccost) AS total_cogs FROM ((invh INNER JOIN invd ON invd.inv_nbr = invh.inv_nbr) LEFT OUTER JOIN (crdh INNER JOIN crdd ON crdh.crduniq = crdd.crduniq) ON invh.inv_nbr = crdh.inv_nbr) WHERE invd.qtyshipped > 0
AND invh.invdate > '2004-05-01'
AND invd.item = crdd.item
GROUP BY invd.item, invd.qtyshipped, invd.extinvmisc, invd.exticost, invh.inv_nbr, invh.customer, invh.invdate, crdh.crd_nbr;