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.