Sql Outer join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sql Outer join

Author

Topic

canasdaq_deals
Starting Member

2 Posts

Posted - 2007-03-01 : 09:49:17

Hello,
I have been working on this sql and spent quite a bit, but could not get the result. Please help me.
I want to display the result even if there are no corresponding rows in CPSeleSortorder table.

Here is the sql.

Select COVERAGE = (Select rtrim(DESCRIPT) From PX050000 Where PX050000.COVERAGE = VW_VEH_COVERAGES_INFO.COVERAGE and INSLINE = 'CA'),
Deductible = (Case VW_VEH_COVERAGES_INFO.COVERAGE

When 'DOCUMP' then
(Select USCDE02 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='DOCUMP')
When 'HIRECM' then
(Select USCDE09 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'HIRECO' then
(Select USCDE11 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'LIAB' then
(Select USCDE12 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'TRLCOL' then
(Select USCDE08 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='TRLCOL')
When 'UMPD' then
(Select USCDE02 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='UMPD')

Else (Select '-1')
End),
Limit = (Case VW_VEH_COVERAGES_INFO.COVERAGE

When 'DOC-MP' then
(Select USCDE14 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'DOC-UM' then
(Select USIND06 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'DOC-UN' then
(Select USIND05 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'DOCUMP' then
(Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='DOCUMP')
When 'DTHBEN' then
(Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='DTHBEN')

When 'HIREUN' then
(Select USIND12 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'LIAB' then
(Select USAMNT05 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'MEDPAY' then
(Select USCDE02 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'NONUM' then
(Select USCDE08 From PR006000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA')
When 'SOUND' then
(Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='SOUND')
When 'TRLCMP' then
(Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='TRLCMP')
When 'TRLCOL' then
(Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='TRLCOL')
When 'TRLSP' then
(Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='TRLSP')
When 'UMPD' then
(Select USAMNT01 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='UMPD')
When 'REG-UM' then
(Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='REG-UM')
When 'REG-UN' then
(Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='REG-UN')
When 'REG-L' then
(Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='REG-L')
When 'REG-MP' then
(Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='REG-MP')
When 'REG-PIP' then
(Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 and insline = 'CA' and coverage='REG-PIP')

Else (Select '-1')
End),
RATEPREM As Premium,
VW_VEH_COVERAGES_INFO.COVERAGE As CoverageCd
From VW_VEH_COVERAGES_INFO left outer join cpselesortorder on VW_VEH_COVERAGES_INFO.COVERAGE = cpselesortorder.COVERAGE
where UNTRECNUM=0 and VW_VEH_COVERAGES_INFO.INSLINE = 'CA' and cpselesortorder.INSLINE = 'CA' and SYMBOL='CPP' AND POLNUM=1218062 AND MODULE=0 AND LOC=00 AND MCO=01 AND PCO=00 Order By SORTORDER Asc

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 10:15:14

The problem is that you have this in your WHERE clause
and cpselesortorder.INSLINE = 'CA'
For rows in VW_VEH_COVERAGES_INFO that do not have related rows in cpselesortorder, the cpselesortorder.INSLINE value will be NULL, so you should put
and cpselesortorder.INSLINE = 'CA'
in the ON clause of the JOIN. You could also change it in the WHERE clause to
and (cpselesortorder.INSLINE = 'CA' OR cpselesortorder.INSLINE IS NULL)

If there are other columns from cpselesortorder in the WHERE clause the same thing applies, but based on what you gave, INSLINE is the only column that I can tell for sure is in that table.

canasdaq_deals
Starting Member

2 Posts

Posted - 2007-03-01 : 11:32:10

Thank you so much for your help. I really appreciate it. The 2nd trick helped solve the issue.

Thanks again.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-01 : 14:36:40

quote:

You could also change it in the WHERE clause to
and (cpselesortorder.INSLINE = 'CA' OR cpselesortorder.INSLINE IS NULL)

Actually, that seems like it will work, and sometimes it does, but it doesn't always .... I have a post somewhere that demonstrates why, I'll see if I can find it ....

EDIT

and here it is:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69936

- Jeff
http://weblogs.sqlteam.com/JeffS

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 14:53:18

Good call Jeff.

canasdaq_deals, you must do it the first way I said before, not the second. So move
and cpselesortorder.INSLINE = 'CA'
to the ON clause of the outer join between VW_VEH_COVERAGES_INFO and cpselesortorder, otherwise VW_VEH_COVERAGES_INFO rows will be missing whenever there is a VW_VEH_COVERAGES_INFO for which there are cpselesortorders but none are for 'CA'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources