CODEWITHSUNDEEP
sqlt-sql
emie
emie

Reputation: 289

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?

Upvotes: 1

Views: 67

Answers (2)

Ezin82
Ezin82

Reputation: 374

An alternative could be using CTE and LEFT JOIN. Try this:

WITH PFM AS (
  SELECT DISTINCT ProductFamily FROM [reference].[ProductFamilyMapping] where (isA = 1)
)
SELECT 
    ID
    ,CASE WHEN PFM.ProductFamily IS NULL THEN 'No' ELSE 'Yes' END AS 'O'
From  [source].[Opportunity] A 
LEFT JOIN PFM ON A.ProductFamily = PFM.ProductFamily

Upvotes: 1

EdmCoff
EdmCoff

Reputation: 3596

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).

Upvotes: 1

Related Questions