Problem linking tables

[chudson007]

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?
Regards,
Ciarán

[Erland Sommarskog]

(ch********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

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.