We have customer and account table and one customer has multiple accounts.
Customer table
customer_ID Name
101 Smith 102 Williams 103 Martin 104 Jack
Account table Account_ID customer_ID Account_Type 201 101 A1 202 101 B1 203 101 C1 301 102 B1 302 102 C1 401 103 A1 402 103 C1 501 104 B1
If one customer has multiple accounts, we select account_type base on this order: A1, C1, and B1
The result will be : Result
customer_ID Account_Type 101 A1 102 C1 103 A1 104 B1
I write the following query: Select c.customer_ID, case when Account_Type in ('A1','B1' 'C1' ) then A1 when Account_Type in (''B1' 'C1' ) then C1 else Account_Type End From customer c join account a on a.customer_ID=c.customer_ID
How can I put the condition: one customer has multiple accounts in this query?
Thanks
Aucun commentaire:
Enregistrer un commentaire