Re: Please Help!! I'm going to JOIN a mental asylum!!

nicholasrolfe_at_yahoo.com.au (orekin) wrote in message news:<6f87d1d0.0406120717.259c651f_at_posting.google.com>...
> I've tried this query with a few DBMS, but they either freeze or tell
> me Ambiguous Outer Join:
>
> SELECT "OEINVD"."ITEM" as "Item #", "OEINVD"."QTYSHIPPED" as "Quantity
> Shipped", "OEINVD"."EXTINVMISC" as "$ Sales", "OEINVD"."EXTICOST" as
> "$COGS", "OEINVH"."INVNUMBER" as "Invoice #", "OEINVH"."CUSTOMER" as
> "Customer", "OECRDH"."CRDNUMBER" as "Credit Note #",
> SUM("OECRDD"."QTYRETURN") as "Quantity
> Returned",SUM("OECRDD"."EXTCRDMISC") as "$ Return",
> SUM("OECRDD"."EXTCCOST") as "$ Return COGS"
>
> FROM (("OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNUMBER" =
> "OEINVH"."INVNUMBER") LEFT OUTER JOIN "OECRDH" ON "OEINVH"."INVNUMBER"
> = "OECRDH"."INVNUMBER") INNER JOIN "OECRDD" ON "OECRDH"."CRDUNIQ" =
> "OECRDD"."CRDUNIQ"
>
> WHERE "OEINVD"."QTYSHIPPED">0 AND "OEINVH"."INVDATE">20040501 AND
> "OEINVD.ITEM" = "OECRDD.ITEM"
>
> GROUP BY "OEINVD"."ITEM", "OEINVD"."QTYSHIPPED",
> "OEINVD"."EXTINVMISC",
> "OEINVD"."EXTICOST","OEINVH"."INVNUMBER","OEINVH"."CUSTOMER",
> "OEINVH"."INVDATE", "OECRDH"."CRDNUMBER";
>
> ....
>
> OEINVH & OEINVD represent invoices, and are linked to each other via
> inner join on INVNUMBER
>
> OECRDH and OECRDD represent credit notes and are linked to each other
> via inner join on CRDUNIQ
>
> What I'm trying to do is list all the invoice detail lines (OEINVD)
> with credit note information if applicable. My first problem is that
> the detail level information for credit notes is in OECRDD. The only
> way I can see to link from OEINVD is:
>
> OEINVD.INVNUMER = OEINVH.INVNUMBER
>
> OEINVH.INVNUMBER OuterLeft OECRDH.INVNUMBER
>
> OECRDH.CRDUNIQ = OECRDD.CRDUNIQ
>
> OEINVD.ITEM = OECRDD.ITEM
>
> I need that last join, otherwise the recordset would have incorrect
> summing on the credit note side.
>
> My second issue is this - not every invoice has a credit note, and
> just to make life difficult, there can be > 1 credit note against an
> invoice! So in other words an invoice could have 0,1 or more credit
> notes against it. Where there is > 1 credit note against an invoice I
> want to sum the credit note fields.
>
> I've come to a dead end, how can I get this working ?
> Thanks
> Bill

i won't attempt to improve on Mr. Celko's query. but will recommend (...teach 'em to fish...) somebody else's book: "SQL Tuning" by Dan Tow. whether one buys his formula, or not, the text explains the notion of joins lucidly.

robert Received on Thu Jun 17 2004 - 03:37:50 CEST