SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select a specific one to many join record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

darms21
Yak Posting Veteran

54 Posts

Posted - 04/05/2013 :  12:01:25  Show Profile  Reply with Quote
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

Edited by - darms21 on 04/05/2013 12:07:55

stepson
Constraint Violating Yak Guru

Romania
280 Posts

Posted - 04/05/2013 :  13:13:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000