Problems with joins

[chudson007]

I'm having trouble with joins...

TableA includes the fields name, order_number and value1.
Tableb includes the fields order number and value2

I'm joining on Order Number.
I want to return Name, TableA.order_number, TableB.order_number, value1
and value2.

I want to return all order_numbers from TableA and any from TableB that
are in TableA.

My script currently has TableA LEFT OUTER JOIN TableB On
TableA.order_number = TableB.order_number

I know it is incorrect because the sum of TableA.Value1 is 100, but
after my left outer join with TableB the sum of Value1 is 50.

Any ideas?

Regards,
Ciarán

[Hugo Kornelis]

On 12 Apr 2005 09:38:44 -0700, ch********@hotmail.com wrote:

I'm having trouble with joins...

TableA includes the fields name, order_number and value1.
Tableb includes the fields order number and value2

I'm joining on Order Number.
I want to return Name, TableA.order_number, TableB.order_number, value1
and value2.

I want to return all order_numbers from TableA and any from TableB that
are in TableA.

My script currently has TableA LEFT OUTER JOIN TableB On
TableA.order_number = TableB.order_number

I know it is incorrect because the sum of TableA.Value1 is 100, but
after my left outer join with TableB the sum of Value1 is 50.

Any ideas?

Regards,
Ciarán

Hi Ciarán,

Can you post the complete query, please? Even better would be if you
include the DDL (CREATE TABLE statements) for the tables as well,
preferably including some sample data (posted as INSERT statements).

That will allow us to review your code, run some tests and come up with
a suggestion.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[chudson007]

Here is the script

The sum of Shpt_Leg_List_CLC changes between Script1 and Script2 and
the only change I think I have made is the join to TableB

Script1
SELECT Customer_Number AS Customer_Number, ID_Code AS ID_Code,
LEFT(Ship_Day, 4) AS Ship_Month, SUM(Shpt_Leg_List_CLC) AS
Shpt_Leg_List_CLC,
SUM(Shpt_Leg_Net_CLC) AS Shpt_Leg_Net_CLC,
SUM(Shpt_Leg_List_LC) AS Shpt_Leg_List_LC, SUM(Shpt_Leg_Net_LC) AS
Shpt_Leg_Net_LC,
SUM(Shpt_Leg_Ref_List) AS Shpt_Leg_Ref_List,
SUM(Shpt_Leg_Ref_Net) AS Shpt_Leg_Ref_Net, SUM(Shpt_Leg_Prod_Units) AS
Shpt_Leg_Prod_Units,
SUM(Shpt_Leg_Optn_Units) AS Shpt_Leg_Optn_Units,
SUM(Shpt_Leg_Supt_Units) AS Shpt_Leg_Supt_Units,
Shpt_Leg_MCC_Units AS Shpt_Leg_MCC_Units,
Department, Misc_Charge_Code AS TableA_DataV1, Order_Section
INTO TableA_DataV2
FROM TableA
GROUP BY Customer_Number, ID_Code, LEFT(Ship_Day, 4),
Shpt_Leg_MCC_Units, Department, Misc_Charge_Code, Order_Section
ORDER BY LEFT(Ship_Day, 4), Customer_Number, ID_Code, Department,
Shpt_Leg_MCC_Units, Misc_Charge_Code

Script2
SELECT TableA.Customer_Number AS Customer_Number, TableA.ID_Code AS
ID_Code,
LEFT(TableA.Ship_Day, 4) AS Ship_Month,
SUM(TableA.Shpt_Leg_List_CLC) AS Shpt_Leg_List_CLC,
SUM(TableA.Shpt_Leg_Net_CLC) AS Shpt_Leg_Net_CLC,
SUM(TableA.Shpt_Leg_List_LC)
AS Shpt_Leg_List_LC, SUM(TableA.Shpt_Leg_Net_LC)
AS Shpt_Leg_Net_LC,
SUM(TableA.Shpt_Leg_Ref_List) AS
Shpt_Leg_Ref_List, SUM(TableA.Shpt_Leg_Ref_Net)
AS Shpt_Leg_Ref_Net,
SUM(TableA.Shpt_Leg_Prod_Units) AS Shpt_Leg_Prod_Units,
SUM(TableA.Shpt_Leg_Optn_Units) AS
Shpt_Leg_Optn_Units, SUM(TableA.Shpt_Leg_Supt_Units)
AS Shpt_Leg_Supt_Units, TableA.Shpt_Leg_MCC_Units
AS Shpt_Leg_MCC_Units, TableA.Department,
TableA.Misc_Charge_Code AS TableA_DataV1,
TableA.Order_Section,
TableB.Sales_Order_Number
INTO TableA_DataV2
FROM TableA LEFT OUTER JOIN
TableB ON TableA.Order_Section =
TableB.Sales_Order_Number
GROUP BY TableA.Customer_Number, TableA.ID_Code, LEFT(TableA.Ship_Day,
4),
TableA.Shpt_Leg_MCC_Units, TableA.Department,
TableA.Misc_Charge_Code, TableA.Order_Section,
TableB.Sales_Order_Number
ORDER BY LEFT(TableA.Ship_Day, 4), TableA.Customer_Number,
TableA.ID_Code,
TableA.Department, TableA.Shpt_Leg_MCC_Units,
TableA.Misc_Charge_Code

[Hugo Kornelis]

On 13 Apr 2005 01:37:09 -0700, ch********@hotmail.com wrote:

Here is the script

The sum of Shpt_Leg_List_CLC changes between Script1 and Script2 and
the only change I think I have made is the join to TableB

(snip)

Hi Ciarán,

Does only the sum of Shpt_Leg_List_CLC change, or do all the other sums
change as well?

Did you factor in that the total from one group in query 1 may be
divided over several groups in query 2, due to the extra column in the
GROUP BY clause (TableB.Sales_Order_Number)?

Are you sure that the total from query 1 is HIGHER than the total from
query 2? I could explain a LOWER total, not a HIGHER total!

I see no obvious problems with the code you posted. If you want me to
investigate this further, you'll really have to poste CREATE TABLE and
INSERT statements as well, so that I can reproduce the problem on my
computer.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)