Inner Join with similar tables bring different results

[WilliamWales]

Hi

I have a mystery that I need some help.

Table: Order_Header

Fields:Fields:O rder_number(PK) ,Client_Account _Number,Order_D ate

Table: Order_Detail (PK Order_number+Li ne_number)

Order_number,Li ne_number,Produ ct_Ordered,Prod uct_Ordered_Uni ts

Table: Package_Header


Fields:Package_ Number(Pk),Orde r_Number,Pallet _Number

(Note: No more than ONE order per package, but an order can have several packages)

Table: Package_Detail( pk = Package_Number+ Line_number)


Fields:Package_ Number,Line_num ber,Product_Ord ered_Units


The results I want:


Client Acc Number,Total Pallets,Total Packages,Total Units,Month Shipped


The SQL that will produce the right results:

       SELECT   ph.Client_Account_Number, 
                COUNT (DISTINCT (ch.Pallet_Number)) AS Total_Pallets,
                COUNT (DISTINCT (ch.Package_Number)) AS Total_Packages,
                SUM(cd.Product_Ordered_Units) AS Total_Units,
                to_char(pi.Order_Date,'Month') as Month_Shipped

       FROM Order_Header ph

       Inner Join Package_Header ch ON ph.Order_Number = ch.Order_Number

       INNER JOIN Package_Detail cd ON cd.Package_Number = ch.Package_Number

       Where to_char(ph.Order_Date,'YYYY') = '2012'

       Group by ph.Client_Account_Number,to_char(ph.Order_Date,'Month')

The SQL that will produce the wrong results (the quantities are several times OVER what they are suppose to be):

       SELECT   ph.Client_Account_Number, 
                COUNT (DISTINCT (ch.Pallet_Number)) AS Total_Pallets,
                COUNT (DISTINCT (ch.Package_Number)) AS Total_Packages,
                SUM(cd.Product_Ordered_Units) AS Total_Units,
                to_char(pi.Order_Date,'Month') as Month_Shipped

       FROM Order_Header ph

       Inner Join Package_Header ch ON ph.Order_Number = ch.Order_Number

       INNER JOIN Order_Detail cd ON cd.Order_Number = ph.Order_Number

       Where to_char(ph.Order_Date,'YYYY') = '2012'

       Group by ph.Client_Account_Number,to_char(ph.Order_Date,'Month')

The difference is ofcourse the addition of the Order_Detail table as opposed to the Package_Detail table.
Can't understand why....

[Anas Mosaad]

It seems there is another table you are using in the SQL referenced as pi but is not in your queries.

Anyway, a simple diagram showing the relations will help us a lot to know the relation between the tables.

[WilliamWales]

Thanks for the reply I will try a diagram later on.....its all very simple.....and the pi should it say ph.

[Rabbit]

It's because you have joined to package header. Now you have multiple rows with the same order number. If you now go and join to order details, you duplicate the order details because of that.

On the other hand, while you have multiple rows with the same order number, you have unique package numbers. So when you join to package details, there's no duplication.