469,271 Members | 1,776 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Problems with joins

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

Jul 23 '05 #1
3 963
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)
Jul 23 '05 #2
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

Jul 23 '05 #3
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)
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Randell D. | last post: by
4 posts views Thread by michaelnewport | last post: by
5 posts views Thread by Scott | last post: by
reply views Thread by news.microsoft.com | last post: by
2 posts views Thread by narendra vuradi | last post: by
36 posts views Thread by TC | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.