3

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.

0
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
2
  • I think in your second example it should read [Exists] = COALESCE([bph].[uuid],'No') Feb 16 '16 at 20:25
  • 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. Feb 17 '16 at 8:11
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.