Return values from Left Table Only Once

Question

This is my table structure and a sample query. However, my query returns these results:

ID event1 Exists
1 cycle No
1 cycle Yes
2 run Yes
3 walk No

As you can see ID 1 is returned twice, a yes and a no value. I only want each item from event1 returned 1 time in my query. Desired output is:

ID event1 Exists
1 cycle Yes
2 run Yes
3 walk No

Here is sample table structure and my query (which please feel free to completely re-write), what must be altered in order to return my desired result set?

create table #robschneider
(
 ID int IDENTITY(1,1) PRIMARY KEY
 ,event1 varchar(100)
 ,uuid int
)
create table #bigponyhorses
(
 ID int IDENTITY(1,1) PRIMARY KEY
 ,uuid int
 ,empid varchar(10)
)
Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')
Insert Into #bigponyhorses VALUES ('1','mn12'), ('2','mn12'), ('3', 'cr23'), ('1', 'bb11')
Declare @empid varchar(10)
Set @empid = 'mn12'
select
DISTINCT
[rs].[ID],
[rs].[event1],
case 
 when [bph].[empid] = @empid AND [bph].[uuid] is not null then 'Yes' else 'No' 
end as [Exists]
from #robschneider rs
LEFT join #bigponyhorses bph
on [rs].[uuid]=[bph].[uuid]

If it exists, I want a yes returned, if it does not exist I want a no returned.

Answer 1

You can replace LEFT by OUTER APPLY.

Query:

SELECT [rs].[ID]
 , [rs].[event1]
 , [Exists] = CASE WHEN [bph].[uuid] IS NOT NULL THEN 'Yes' ELSE 'No' END
FROM #robschneider rs
OUTER APPLY (
 SELECT TOP(1) [uuid]
 FROM #bigponyhorses 
 WHERE [empid] = @empid 
 AND [rs].[uuid] = [uuid]
) [bph]

Or:

SELECT [rs].[ID]
 , [rs].[event1]
 , [Exists] = COALESCE([bph].[Exists], 'No')
FROM #robschneider rs
OUTER APPLY (
 SELECT TOP(1) [Exists] = 'Yes' 
 FROM #bigponyhorses 
 WHERE [empid] = @empid 
 AND [rs].[uuid] = [uuid]
) [bph]

Or:

SELECT [rs].[ID]
 , [rs].[event1]
 , [Exists] = COALESCE( 
 (
 SELECT TOP(1) [Exists] = 'Yes' 
 FROM #bigponyhorses 
 WHERE [empid] = @empid 
 AND [rs].[uuid] = [uuid]
 )
 , 'No')
FROM #robschneider rs

Output:

ID event1 Exists
1 cycle Yes
2 run Yes
3 walk No

• nospaceshuttle leftinthisnatio: I think in your second example it should read [Exists] = COALESCE([bph].[uuid],'No')
• Julien Vavasseur: No, this is like a left join, the column exists but can be null when there is no match. In this case, it is either Yes or Null and coalesce replace Null by No.

Answer 2

This could be solved with an EXISTS subquery

select
 rs.ID,
 rs.event1,
 [Exists] =
 case when exists
 ( select *
 from #bigponyhorses as bph
 where rs.uuid = bph.uuid
 and bph.empid = @empid
 ) 
 then 'Yes' else 'No'
 end
from 
 #robschneider as rs ;

or with OUTER APPLY operator:

select
 rs.ID,
 rs.event1,
 [Exists] = coalesce(b.result, 'No') 
from 
 #robschneider as rs
 outer apply 
 ( select top (1) result = 'Yes'
 from #bigponyhorses as bph
 where rs.uuid = bph.uuid
 and bph.empid = @empid
 ) as b ;

The TOP 1 is needed when using OUTER APPLY to make sure you get in the result one row for each #robschneider, even if there are 2 or more matching rows in the #bigponyhorses table. If there is a UNIQUE constraint on #bigponyhorses (uuid, empid), then it's not needed.