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:
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';
3
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
3
I would use conditional aggregation in the HAVING clause and just count any occurences with a type <> savings. If none appear, this customer will be selected.
The “standard” way all RDBMS support is
HAVING COUNT(CASE WHEN type <> 'savings' THEN 1 END) = 0;
More and more RDBMS rather provide a FILTER clause, they are more readable:
HAVING COUNT(*) FILTER(WHERE type <> 'savings') = 0;
MySQL provides the shortest syntax, there you can write SUM(boolean expression):
HAVING SUM(type <> 'savings') = 0;
So the entire query will be:
SELECT
c.name
FROM
customers c
LEFT JOIN accounts a --or INNER JOIN, check what you need
ON c.account_num = a.account_num
GROUP BY
c.name
HAVING
COUNT(CASE WHEN a.type <> 'savings' THEN 1 END) = 0;
--replace by FILTER clause if your RDBMS supports it
Here a demo on Postgres which supports both the FILTER and the CASE option.
A different approach, use EXCEPT:
SELECT name FROM customers WHERE type = 'savings'
EXCEPT
SELECT name FROM customers WHERE type <> 'savings'
Will however return a customer having 2 savings accounts (and nothing else).