Re: Query with Joins problem
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 22 Jun 2006 22:20:23 +0000 (UTC)
(wilhelm.kleu@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Query with Joins problem
- From: wilhelm . kleu
- Query with Joins problem
- Prev by Date: Re: SQL Join
- Next by Date: Re: Ambiguous Column Names in Multi-Table Join
- Previous by thread: Query with Joins problem
- Next by thread: Very Poor Performance - Identical DBs but Different Performance
- Index(es):
Relevant Pages
|
Loading
