I need to return all customers which have made savings every week.
Table1: Customers - AccountNo, CustName, CustAddr
Table2: Savings - DepositNo, WeekNo, AccountNo, DepositAmt
An excellent question. Off the top of my head, the answer would be --
select distinct CustName from Customers where AccountNo in ( select AccountNo from Savings group by AccountNo having count(distinct WeekNo) = ( select count(distinct Weekno) from Savings ) )
This should be fairly efficient, because the sub-subquery which counts the number of distinct week numbers in the Savings table will be executed only once; this gives a static number to be tested in the HAVING clause in the subquery, to find all the account numbers which qualify. This set of account numbers then determines which customers qualify, and the DISTINCT on the outer query ensures that customers who might have more than one such qualifying account are reported only once.
When I tried testing this in Access 97, I discovered that Access 97 does not support COUNT DISTINCT. However, all is not lost. Access allows you to run a query against a saved query -- in effect, a view mechanism. The problem can also be solved with views, but I'll describe the process using Access queries instead. Note that Access queries do not actually save any results; when you query a query in Access, the database engine "combines" the query definitions "under the covers" and only the combined query is actually executed.
The first step is to create a query for distinct week numbers --
select distinct WeekNo from Savings
Save this query as DistinctWeeks_Q.
Now find the week numbers for each account --
select distinct AccountNo, WeekNo from Savings
Save this query as DistinctAccountWeeks_Q.
Now you can formulate the final query --
select CustName from Customers where AccountNo in ( select AccountNo from DistinctAccountWeeks_Q group by AccountNo having count(*) = ( select count(*) from DistinctWeeks_Q ) )
There's only one other problem that might trip you up -- what if there's a week where nobody made a deposit? The query will still run, and still give results, but it will be all customers that made a deposit in every week that any customer made a deposit, which isn't exactly the same as every week. You may need to generate a different sub-subquery, for example from a calendar week number table, if you want to catch those gaps.
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.