Select a specific one to many join record

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)

Select a specific one to many join record

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2013-04-05 : 12:01:25

Hello,
Table1
ID NAME
1 A
2 B

TABLE2
ID SITE
1 N/A
1 NY
1 N/A
2 CA
2 CA

With the above two tables in mind - how can I join Table1 on Table2, returning 1 row per ID and ensuring that 1 row per ID is always a non N/A value. In the case of ID 2, I dont care which CA it returns, my only desire is to not get the N/A row.

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-04-05 : 13:13:46

Hello
try this

;with Table1
as
(
select 1 as ID,'A' as Name
union all
select 2,'B'
)
,Table2
AS
(
select 1 as ID , 'N/A' as Site
union all
select 1 , 'NY'
union all
select 1 ,'N/A'
union all
select 2, 'CA'
union all
select 2, 'CA'
)

select T1.*
, OneRow.Site
from Table1 as T1
outer apply
(
select top 1 T2.Site
from Table2 as T2
where T1.id=T2.id
and T2.Site<>'N/A'
) as OneRow

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources