Left join with distinct values from second table

Question

I'm trying to join two tables, one is a table of tags, and another is a table that bridges tagID and slotID. I want to create a sproc that returns the list of tags, and a column of slots that use those categories.

tagDetails

tagID   tagDescription

  1      red  
  2      blue  
  3      green  
  4      purple

tagBridge

tagID  slotID

  1         1    
  1         3  
  2         5  
  3         1  
  4         1  

and i'd like the sproc to return a result like this if it takes a slotID parameter of 1

tagID   tagDescription    slotID
  1      red           yes  
  2      blue           no  
  3      green         yes  
  4      purple        yes  

i made a sproc like this but i know its not really going to work properly

SELECT tagDetails.tagID, tagDetails.tagDescription, tagBridge.slotID
FROM tagDetails
LEFT JOIN tagBridge
ON tagDetails.tagID = tagBridge.tagID
where tagBridge.slotID = 1

any ideas?

Answer 1

easy:

  Select tagID,   tagDescription,
    Case When Exists 
      (Select * From tagBridge
       Where tagId = t.TagId
          And slotId = @SlotId)
     Then 'yes' else 'no' End SlotId
  From tagdetails t

Answer 2

Use:

   SELECT td.tagid,
          td.tagdescription,
          CASE 
            WHEN tb.slotid = 1 THEN 'Yes'
            ELSE 'No'
          END AS slotid
     FROM TAGDETAILS td
LEFT JOIN TAGBRIDGE tb ON tb.tagid = td.tagid