June 18, 2007 at 5:50 pm
I have to join a table A with itself 3 times, something like :
SELECT a1.field2 , a2.field2 , a3.field2 , a4.field2
FROM a AS a1
FULL OUTER JOIN a AS a2
ON (.....)
FULL OUTER JOIN a AS a3
ON (.....)
FULL OUTER JOIN a AS a4
ON (.....)
WHERE a1.field1=val1 AND a2.field1 = val2 AND a3.field1 = val3 AND a4.field1 = val4
BUT, the problem with this query is that if there is no record satisfying the last condition in the WHERE clause, the result will be an empty one.
What I really need is something like :
field2 field2 field2 field2
---------------------------------------------------------
VAL1 VAL2 VAL3 NULL
(this assuming of course that there is not any row satisfying the last condition)
Practically speaking, I have to join a non-empty table with an empty one and getting a non-empty result (rows containing info from the non-empty table in the fields from the non-empty table with NULLs for the fields corresponding the empty table
Note2 :
1. Any condition of the 4 could not be satisfied (or even more of them simultaneously..)
2. I have to write a view, not a stored procedure
I assume this is pretty basic, I really don't know how could I write this ...
Please HELP !
June 18, 2007 at 6:02 pm
SELECT a1.field2 , a2.field2 , a3.field2 , a4.field2
FROM a AS a1
LEFT OUTER JOIN a AS a2
ON (.....) AND a2.field1 = val2
LEFT OUTER JOIN a AS a3
ON (.....) AND a3.field1 = val3
LEFT OUTER JOIN a AS a4
ON (.....) AND a4.field1 = val4
WHERE a1.field1=val1
Result still will be empty if there are no rows WHERE a1.field1=val1
_____________
Code for TallyGenerator
June 18, 2007 at 6:12 pm
this solves ~ 90% of situations - thank you very much !!
June 20, 2007 at 8:39 am
Take a look at COALESCE in BOL. You may be able to use it in the WHERE clause (and other places as well) to get exactly the results you desire.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply