Search code examples
sqlfilter

A query to show a list of customers with only 1 specific product

I am fairly new to writing queries so I am not sure if what I am trying to do is even possible. I want to write a query that gives me a list of customers who only have a savings account and no other products.

This is how the data is saved:

enter image description here

I want the result to show me ONLY Sarah and Shelly

but the best I can come up with shows me Jane, Sarah and Shelly- How do I filter out the Jane results?

SELECT      A.customer_name
FROM        A.Table1
INNER JOIN  B.Table2
ON          A.acct_number = B.acct_number
WHERE       B.account_type = 'savings';
Solution

  • You can group by name to get a count of everyone's accounts, then filter that to people with only one account using having (it's like where but filters after the grouping).

    Then we can also check what type of account they have. Normally you can't do this with a group by because each group would have multiple rows with different types. But because we're looking for people with just one row we can use max(type), since there's only one type per group it will always return just type.

    (Most databases also have a way to concatenate all the values in a group, and that might be a better choice than max, but they're non-standard.)

    select name
from customers c
left join accounts a on c.account_num = a.account_num
group by name
having count(*) = 1 and max(type) = 'savings'

    Demonstration.

    Or you can use an aggregate filter clause to only count their non-savings accounts. Most databases support it, but not MySQL.

    select name
from customers c
left join accounts a on c.account_num = a.account_num
group by name
having count(*) filter(where type <> 'savings') = 0