Re: A Simple SQL Query

Hi, Joins are Cartesian products: Each row in the lefthand table is examined for a possible match with each row in the righthand table.Thus, before the SUM or GROUP BY takes effect, you have a table which looks like this: SD_ITEM SD_QTY PD_ITEM PD_QTY 3 100 3 2 3 1 3 2 3 3 3 2 If you had had another entry in PURCHASE_DETAILS with PD_ITEM = 3, you would have been even more confused. :) I'd just break this up into two separate queries. If SD_ITEM and PD_ITEM are conceptually different attributes, you're playing with fire. -J- } } Can someone help me solve this simple problem ? There are two tables, and } I need a summary of the items. For SUM(SD_QTY) I get the right figure, } but for SUM(PD_QTY) I get the figure multiplied by 3. } } ------------------- Table : PURCHASE_DETAILS } ---------------------------- } PD_ITEM PD_QTY } 3 2 } } ------------------- Table : SALES_DETAILS } ---------------------------- } SD_ITEM SD_QTY } 3 100 } 3 1 } 3 3 } 4 200 } SB 1 } ---------------------- S Q L } -------------------------------------------------------------- } SELECT SD_ITEM, SUM(SD_QTY), SUM(PD_QTY) } FROM SALES_DETAILS, PURCHASE_DETAILS } WHERE SD_ITEM = PD_ITEM (+) } GROUP BY SD_ITEM; } ----------------------- Result } -------------------------------------------------------- } SD_ITEM Sum(SD_QTY) Sum(PD_QTY) } 3 104 6 <-------- This should } be 2 } 4 200 } SB 1 } ----------------------- Thank you in advance } -------------------------------------- } } -------------------==== Posted via Deja News ====----------------------- } http://www.dejanews.com/ Search, Read, Post to Usenet }