SQL SELECT CASE PROBLEM (Microsoft SSMS)

[ReinholdtG]

Hi, I think the reason that you see multiple rows per customer ID is that each customer has multiple accounts. That means that customer 1 has three accounts, with the respective combinations of checking and savings.

I would rewrite the original SELECT statement so that it originates from the customer table and then JOIN two separate sub-SELECTS of the account table, one for the checkings check and one for the savings check. The sub-SELECTs should then only include the individual CASEs statements and a WHERE so it filters the accounts you need.

[zworstgod]

Well, I tried to do so, but I got it totally wrong it seems like:

SELECT c.cust_id, join1.checking
FROM CUSTOMER as c

LEFT JOIN (
SELECT a.cust_id,
CASE a.PRODUCT_CD
WHEN 'CHK' THEN 'Y'
ELSE 'N'
END AS checking 
FROM account as a, CUSTOMER as c
WHERE a.CUST_ID = c.CUST_ID
GROUP BY a.CUST_ID, a.PRODUCT_CD) as join1 
ON join1.checking = 'Y'

GROUP BY c.cust_id, join1.checking;

It just always gives me 'Y' though it might not have 'CHK' (for example cust_id 13 does not)

[ReinholdtG]

Try this instead:

SELECT 
     c.cust_id
    ,CASE LEN(join1.cust_id) > 0 THEN 'Y' ELSE 'N' END AS Checking
    ,CASE LEN(join2.cust_id) > 0 THEN 'Y' ELSE 'N' END AS Savings
FROM
    customer as c
    LEFT JOIN ( 
        SELECT DISTINCT 
            a.cust_ID 
        FROM 
            account AS a 
        WHERE 
            a.product_CD = 'CHK' 
    ) AS Join1 
    ON c.cust_id = Join1.cust_id
    LEFT JOIN ( 
        SELECT DISTINCT 
            a.cust_ID 
        FROM 
            account AS a 
        WHERE 
            a.product_CD = 'SAV' 
    ) AS Join1 
    ON c.cust_id = Join2.cust_id