|
|
Posted by Erland Sommarskog on 06/22/06 22:20
(wilhelm.kleu@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|