ASP/SQL Query Build - Myth Breaking

[Laphan]

Hi Guys

I don't want to keep asking for your help all the time on each individual
query, so could you please help me to break the myths on the following:

1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.
ACCOUNTS includes all of the usual details for customers and suppliers, eg
the account code (as ACCOUNTID), the account name (as NAME), etc.
ACCOUNTBUDGET basically holds a transaction line for each month and it's
corresponding year to store the turnover for that month, eg one row contains
ACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.

Now a lot of the SQL 6.5 tables that I deal with are in this vein and the
usual query is that I want to list all of the ACCOUNTIDs and NAMEs that
exist in the ACCOUNTS table and then show for example what their TURNOVER is
for a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGET
table.

Now if I do a quick query using MS Query all I get are rows that have
related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I have
specified say a certain PERIOD and YEAR.

The main point of my current reporting problem is that I want to show all
the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
particular PERIOD and YEAR.

I'm positive that I have to create a 2 step query/join, but I don't know how
to do it. What is the method? People in this NG, can rattle one up in
seconds, but I just don't see the logic. Can you help me with this query
and let me know how you manage to fathom it.

2) Are there any good web sites that explain in kiddie form how to do this
sort of thing?

I really appreciate your help on this.

Regards

Laphan

[David Portas]

Put your Financial Calendar into a table and join that into your query.

CREATE TABLE Accounts (accountno INTEGER PRIMARY KEY, accountname
VARCHAR(30) NOT NULL UNIQUE)

CREATE TABLE FinancialCalendar (yearno INTEGER CHECK (yearno BETWEEN 2000
AND 2100), periodno INTEGER CHECK (periodno BETWEEN 1 AND 12), PRIMARY KEY
(yearno,periodno))

CREATE TABLE Budgets (accountno INTEGER REFERENCES Accounts (accountno),
yearno INTEGER, periodno INTEGER, amount NUMERIC(10,2) NOT NULL, FOREIGN KEY
(yearno,periodno) REFERENCES FinancialCalendar (yearno,periodno))

SELECT A.accountno, A.accountname, C.yearno, C.periodno,
COALESCE(B.amount,0) AS amount
FROM FinancialCalendar AS C
CROSS JOIN Accounts AS A
LEFT JOIN Budgets AS B
ON A.accountno = B.accountno
AND B.yearno = C.yearno
AND B.periodno = C.periodno
WHERE C.yearno BETWEEN 2001 AND 2004

I recommend Celko's SQL Puzzles and Answers if you want to expand your
repertoire of query techniques:
http://tinyurl.com/353dw

--
David Portas
SQL Server MVP
--