select distinct all the customer who have more than one account [closed]

Question

I have two tables customer and account

customer can have more than one account

i would like to select only the customers who have more than one account in the tables below.. i would like to joind the two tables and get in th result Robert and Manuel who have more than one account

i think we have to use HAVING COUNT(DISTINCT but not sure how?!

thank you in advance!

Answer 1

Please observe the comments on your question.

Here is an ANSI SQL way to achieve what you're looking for:

SELECT MAX(customer.[NAME]) AS [Name]
FROM customer
INNER JOIN account
    ON customer.id = account.[customer.id]
GROUP BY customer.id
HAVING COUNT(*) > 1

This joins the customer and account table together on the customer id (note it's bad practice to use special characters like periods in column and table names, such as your account table's column [customer.id]). Then it groups by the customer.id field (which will appear multiple times as a result of the join to the account table). Next it uses the HAVING clause with the COUNT() aggregate function to filter out any rows that have less than 2 accounts. Finally since you want just the customer.NAME but we're grouping on customer.id, it uses the MAX() aggregate function so we can select customer.NAME (which will always be the same within the grouping of the customer.id for a given customer id).

Note you might want to consider the case where you have two different customers with the same name, e.g. another Robert with customer.id of 1004 (and your account table had two accounts for [customer.id] = 1004). Your request will give you two instances back of the name Robert without a way to distinguish between the two, so you should also select the customer.id as part of the above query.