LEFT JOIN Problem

[alexkeel79]

I am running into a problem when using the left join table. I have one table (table1) that has a count of items and the expense code assigned to that count of items. I have a second table (table2) that gives me additional information related to the expense code. I am using a left join because some of the expense codes in table1 may not be valid. However the sum of the items I get before using the left join is different after I use the left join. Example

SELECT SUM(table1.items)
FROM table1

this = 1046

SELECT SUM(table1.items)
FROM table1 LEFT JOIN table2 ON expenseCode

this = 1014

Since I am using a left join I should get all records from table 1. Any idea why this isn't working?

 

[gmmastros]

You could get different results if there are multiple records in table 2 for a record in table1.

Ex...

Declare @Table2 Table (ExpenseCode Integer)

Insert Into @Table1 Values(1,10)
Insert Into @Table1 Values(2,12)
Insert Into @Table1 Values(3,8)
Insert Into @Table1 Values(4,7)

Insert Into @Table2 Values(1)
Insert Into @Table2 Values(1)
Insert Into @Table2 Values(2)
Insert Into @Table2 Values(4)


Select Sum(Table1.Items) 
From @Table1 table1

Select Sum(Table1.Items)
From	@Table1 Table1
		Left Join @Table2 Table2 On Table1.ExpenseCode = Table2.ExpenseCode

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[alexkeel79]

Yeah that is what I thought as well but the number is lower than it should be and if there were duplicates the number would be larger.

 

[gmmastros]

Are there an values in the item field that are negative?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom