Each has a date in smalldatetime format.
TableA has a qty field in int format.
TableB also has a nvarchar field in which the date is in the format
YYYYMMDD
A simple Query on TableA sum Qty returns a result of 100.
However, When I do a left outer join from TableA to TableB on Date, the
sum of Qty on TableA becomes massivley larger.
What could be causing this?
Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have two tables, TableA and TableB.
> Each has a date in smalldatetime format.
> TableA has a qty field in int format.
> TableB also has a nvarchar field in which the date is in the format
> YYYYMMDD
> A simple Query on TableA sum Qty returns a result of 100.
> However, When I do a left outer join from TableA to TableB on Date, the
> sum of Qty on TableA becomes massivley larger.
> What could be causing this?
Apparently there is more than one row in TableB with the same date
as in TableA.
This should be evident if you replace SUM(qty) with *, so that
you see all rows in the query.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Please learn that rows are not records and columns are
not fields.
>> TableB also has a nvarchar field in which the date is in the format
YYYYMMDD <<
Why? It is redundant. It is in the wrong data type. Do you actively
seek to make queries run longer and be more error prone? What
constriants do you have on that column to prevent illegal dates?
>> When I do a left outer join from TableA to TableB on Date, the sum
of Qty on TableA becomes massivel larger. <<
DATE is a reserved word in SQL and too vague to be proper data element
name. It will be because the same value appears multiple times in
TableB and you get a CROSS JOIN effect.
No comments:
Post a Comment