mardi 8 mai 2018

Customers have multiple account

We have customer and account table and one customer has multiple accounts.

Customer table

customer_ID Name enter image description here 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