join with case - 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 2000 Forums

SQL Server Development (2000)

join with case

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-03-01 : 09:28:45

I have two tables that I need to extract data from.
(cardfile) customers
(sprincipal) events

I want to join on iaddress in 90% of cases unless
iparent is NOT NULL then I want to use that value and
do a second pass at the cardfile to get the right
customer information.

Here is what I started with.

Thanks in advance for any help.

select a.iaddress,
b.*
from sprincipal a
inner join cardfile b on
case b.iparent
when null then a.iaddress=b.iaddress
else b.iparent=a.iaddress

slow down to move faster...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 09:38:15

You can do this in the WHERE clause instead of a JOIN clause:

SELECT a.iaddress, b.*
FROM sprincipal a, cardfile b
WHERE IsNull(b.iparent, b.iaddress)=a.iaddress

CASE can't be used to generate the structure of the join, only the values. I'm not sure this will work:

select a.iaddress, b.*
from sprincipal a inner join cardfile b on
IsNull(b.iparent, b.iaddress)=a.iaddress

Even if it does work, it'll probably run just as well as the WHERE syntax.

Edited by - robvolk on 03/01/2002 09:42:34

skillile
Posting Yak Master

208 Posts

Posted - 2002-03-01 : 09:54:37

I need to check both ways for data return
and accuracy but this seemed to return.

Thanks for the other solution.

select a.iaddress,
b.*
from sprincipal a
inner join cardfile b on
case
when b.iparent is null then b.iaddress
when b.iparent is NOT NULL then b.iparent
end = a.iaddress

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources