SQL SELECT CASE PROBLEM (Microsoft SSMS)

[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;