MS SQL Find a Value in entire column and return true if found

New to SQL trying to find how to do the following :

I have a ProductID# ABC123 (Product) I need to check the “workflow” (Workflow) column and see if the Product ID was ever “extended” – there are various workflow steps – Need to check the entire workflow column, if a ProductID was ever “extended” I need to mark “yes”

Ex :

Product ID    Workflow Extended?    
-------------------------------
ABC123        "Yes"
ABC234        "No"
ABC345        "Yes"

Please Help!

Answer

Usually you’d select the products from the product table and then check whether there is an extended entry for them in the workflow steps table. This is done with INor EXISTS.

select
  product_id,
  name,
  case when product_id in
              (select product_id from workflow_steps where workflow = 'extended')
       then 'yes'
       else 'no'
  end as extended
from products
order by product_id;

This avoids having to read all rows from the (possibly large) workflow steps table. This table should have an index on the two columns we are interested in to make the lookup happen quickly. The order of the two columns shouldn’t matter, I think, but you can play safe and create both indexes. Then in the query’s execution plan check which index is used and drop the other one:

create index idx1 on workflow_steps(product_id, workflow);
create index idx2 on workflow_steps(workflow, product_id);