emie emie - 6 months ago 112
SQL Question

SQL Query with Case Statement

I want to check if the customer has a specific product. If they do, return yes. If they do not, return no.

So, I wrote a query that returns that. However, I do not want to type in

ProductFamily
name. I would like to interjoin.

SELECT
ID,
(CASE
WHEN ProductFamily IN ('Product')
THEN 'Yes'
ELSE 'No'
END) AS 'O'
FROM
[source].[Opportunity] A


The following query lets me know if the
ProductFamily
mapping:

SELECT *
FROM [reference].[ProductFamilyMapping]
WHERE (isA = 1)
ORDER BY productfamilyname


How do you inter join a Case statement?

Answer Source

You can put a subquery in the IN statement rather than hardcoded text, although you will need to specify the column name (i.e. "SELECT mycolumn" rather than "SELECT *").

SELECT 
    ID
    ,(CASE 
        WHEN  ProductFamily IN 
        (SELECT ProductFamily FROM [reference].[ProductFamilyMapping] where (isA = 1))
          THEN 'Yes'
        ELSE 'No' END) as 'O'
From  [source].[Opportunity] A 

(I am assuming the column name in [reference].[ProductFamilyMapping] is also ProductFamily).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download