Query with Joins problem

[wilhelm.kleu]

Hello

Let me explain the problem I am having:
I have two tables, data_t and a_data_t
a_data_t is the archive table of data_t

The two tables are exactly the same.

In the table values are stored:
Value (A numeric value)
Code (A text code to identify a report with data)
Line (The line number)
Col (The Col Number)
EDate (The date of entry)
Grp (A number of a group the data belongs to)

I want to get the value from data_t minus the value from a_data_t with
the same Code, Line and Col but with a different EDate (To view the
variance).

Here is my statement:

select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) as
value from data_t d1
full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.line
and d1.col = d2.col
where
d1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26
and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'
order by d1.line, d1.col

It works fine EXCEPT when there is a value in either of the tables that
isn't in the other one, then a value is not given.

Example:
data_t doens't have a value for line=1 and col=2 and grp=26 and Code =
'XC001' and EDate = '2006/06'
a_data_t has the value of 50000 for the same details (Except Edate of
'2006/5')
Instead of returning -50000 it doesn't return anything.

I hope I could explain it correctly.
Any help will be greatly appreciated.

Thanks.

[Erland Sommarskog]

(wi**********@gmail.com) writes:

Here is my statement:

select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) as
value from data_t d1
full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.line
and d1.col = d2.col
where
d1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26
and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'
order by d1.line, d1.col

It works fine EXCEPT when there is a value in either of the tables that
isn't in the other one, then a value is not given.

This is because thw WHERE clause nullifiles the benefit of the full
join. The full join operation bulids a table which consists of the
union of all rows in both tables, and when a row in one table does
not have a match in the other, all columns for that other table are
NULL.

Then you add a WHERE condition where you filter away all NULL values,
so you only get rows that are in both tables.

Try replacing WHERE with AND and see what happens. I'm not sure this
will give the desired result, but without knowledge of the keys it's
a bit difficult to say what you are looking for.

A standard suggestion for this sort of questions is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it easy to copy and paste and develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx