SQL Server – Return Values from Left Table Only Once

sql serversql-server-2008-r2t-sql

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.

Best Answer

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
Related Question