Re: Help! Very complex SQL statement required.
Leonid Schavelev schrieb:
> FIRMS - the list of organizations;
> BANKS - the list of banks;
> ACCOUNTS - the list of references between them, i. e.
> each record corresponds to the certain firm's account
> in the certain bank.
> SNIP
> I'd like to obtain the list of rows, where every bank's ID
> will be corresponded to the NUMBER OF FIRMS, which keep at
> least one account with this bank. Not the whole number of
> accounts - such statement would be quite simple:
>
> SELECT BANKS.ID, COUNT(*)
> FROM BANKS, FIRMS, ACCOUNTS
> WHERE BANKS.ID=ACCOUNTS.B_ID AND FIRMS.ID=ACCOUNTS.F_ID
> GROUP BY BANKS.ID> SNIP
Leonid,
the first solution I can think of, is to break down the task in two
steps:
1st step:
Obtain a unique list of bank - firm relationships
SELECT UNIQUE BANKS.ID, FIRMS.ID
FROM BANKS, FIRMS, ACCOUNTS
WHERE BANKS.ID=ACCOUNTS.B_ID AND FIRMS.ID=ACCOUNTS.F_ID
INTO TEMP UNI_LIST;
2nd step:
Do the count
SELECT BANKS.ID,COUNT(*)
FROM UNI_LIST
GROUP BY BANKS.ID;
3rd step:
Drop the temp table:
DROP TABLE UNI_LIST;
There may be more sophisticated solutions for your problem, but this
should
do the trick as well, I hope.
Regards,
Roland