Query with joins

[rekenaar]

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.

 

[SQLDenis]

what happens when you change WHERE to AND

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[rekenaar]

Mmmm. It seems to give me the exact same values when I change the WHERE to AND.