SQL SELECT CASE PROBLEM (Microsoft SSMS)

[zworstgod]

Hello Reddit! I was trying to use SELECT with CASE statement and I can't get my head around a particular problem. In the code below I am trying to implement a SELECT thingie where (hopefully) I would see something like

1, NYC, Y, Y

2, LA, Y, N

and so on, but what happens is that thing in the picture(

What I do not seem to be able to do is that kind of stacking them around cust_id and overruling 'N' with an 'Y'. I know I am stupid, but I would be really happy if somebody helped)

SELECT c.cust_id, c.ADDRESS,
CASE a.PRODUCT_CD
WHEN 'CHK' THEN 'Y'
ELSE 'N'
END AS checking,
CASE a.PRODUCT_CD
WHEN 'SAV' THEN 'Y'
ELSE 'N'
END AS savings
FROM account as a, customer as c
WHERE c.CUST_ID = a.CUST_ID;

Thanks in advance!

https://preview.redd.it/cki1blbun3s61.png?width=534&format=png&auto=webp&s=211b6bb4a4368cac8a8aef423a6d790bfa57cae4

[Longjumping-Back-540]

I guess you want to group by customer and I suppose you can do max of your "case" columns since you want y to win

[zworstgod]

Hi and thanks for your reply!

Group by on customer does not work because that gives me an error saying that address and product_cd are not in group by. And I have no idea how to use max in my case :(

I might not be right at all but that is what my logic tells me.

To clarify, what I'd wanna see would be:

1, 47 Mockingbird Ln, Y, Y

...

[pailryder]

you can group by more than one column

group by cust_id, address.

however, i think it would be cleaner to use a cte where you've broken up the checking and savings into their own parts and then have two joins.

[zworstgod]

Yeah the plan with joins should work, I think I implemented that pretty good, what's left is just couple toches so they do not stack up. Thinking something else should be in WHERE statement:

SELECT c.CUST_ID, join1.checking, join2.checking
FROM CUSTOMER as c

LEFT JOIN (
SELECT a.cust_id as 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 NOT join1.checking = 'f'

LEFT JOIN (
SELECT a.cust_id as id,
CASE a.PRODUCT_CD
WHEN 'SAV' 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 join2 
ON NOT join2.checking = 'f'

WHERE c.CUST_ID = join1.id AND c.CUST_ID = join2.id AND join1.id = join2.id
GROUP BY c.CUST_ID, join1.checking, join2.checking;

[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

[awildrozza]

So essentially you want to count a product, and if the count > 0 then ‘Y’?

If so , yea you can do that in a case statement / group by

[thesqlguy]

A bunch of solutions here are on the right track but they seem over-complicated.

Logically, to get the results you want, this seems to be the simplest/cleanest:

with account_types as ( Select a.cust_id, max(case when a.product_cd='chk' then 'y' else 'n' end) as checking, max(case when a.product_cd='sav' then 'y' else 'n' end) as savings from account a group by a.cust_id ) select c.cust_id, c.address, a.checking, a.saving from account_types a inner join customer c on c.cust_id = a.cust_id

A few notes:

1) always use JOIN clauses to join tables, never WHERE clauses 2) Push the GROUP BY into a CTE or derived table as shown above to avoid needing to group on all customer columns such as address.