How to use GROUP BY with a case statement using a variable or other methods-CodePudding

I want to be able to only select "not created" customers IDs and Customer IDs that are greater than 2 but I don't know how to include these together in the following code:

--DECLARE @Match_customer_ids VARCHAR
--SET @Match_customer_ids = 'Not Created'
--SELECT 

SELECT A.customer_id, 
       CASE WHEN B.customer_id IS NOT NULL
       THEN 'Created'
       ELSE 'Not Created'
       END 
       AS 'Is an Invoice Created'
       

    from customers A
    left join orders B
    on A.customer_id = B.customer_id
    
    WHERE A.customer_id > 2

CodePudding user response：

you can add an OR operator in your WHERE clause.

WHERE A.customer_id > 2 OR is_an_invoice_created = 'Not Created'

CodePudding user response：

Since you want to select only "not created" customers IDs, you don't need to use a case expression, simply add this condition AND B.customer_id IS null to the where clause, then all of the selected IDs will be "not created" and greater than 2.

SELECT A.customer_id, 'Not Created' AS 'Is an Invoice Created'
FROM customers A
LEFT JOIN orders B
ON A.customer_id = B.customer_id
WHERE A.customer_id > 2 AND B.customer_id IS null

See a demo.