Conditional 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 2000 Forums

Transact-SQL (2000)

Conditional Join

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2004-12-09 : 02:54:29

I have the following 3 tables:


Create table a (
   1Code int,
   2ID int,
   SomeData varchar
)

Create table b (
   1Code int,
   SomeName varchar
)

Create table c (
   2ID int,
   SomeName varchar
)

I am wanting a join something like this:


select 1Code, 2ID, SomeData, SomeName
from
a join b on a.1Code = b.1Code join
((select * from 
   (Select a.1Code, 2ID, SomeName
     from a left outer join b on a.1Code = b.1Code) a
  where SomeName is Null) b left outer join
c on b.2ID = c.2ID) d on a.2ID = d.2ID

I know the above is a mess and won't work, perhaps there is an easier way.

What I am wanting is to join a & b where there is a match. Where there is no match between a & b then try to join a & c so that there is a value for SomeName for all records in a.

Scott

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-09 : 03:32:51

Try this:

select
	a.1Code,
	a.2ID,
	coalesce( b.SomeName, c.SomeName ) as SomeName
from
	a
	left join b on a.1Code = b.1Code
	left join c on a.2ID = c.2ID
-- where coalesce( b.SomeName, c.SomeName ) is not null

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources