Re: Help! Very complex SQL statement required.
You'll need to create two queries, one after the other, like a
cascade.
In fact, with just one query it's not possible to get the results you
expect, so you need to do it in two steps:
Query #1:
Select Distinct F_ID, B_ID
From ACCOUNTS
This first step will remove every repeated firm / bank record. You'll
get a set with one firm, one bank.
Query #2
Select B_ID, Count( F_ID ) As QtFirms
From FirstQuery
Group By 1
The point is: Delphi queries don't see each other. You have to move
the result of the first query to another table, using TBatchMove and
calling the new table like FirstQuery (in a network, use random
names). Then, you can trigger the second query having the FirstQuery
table as source (From clause).
I hope this helps,
EBR
On Thu, 14 Aug 1997 05:48:25 GMT, Leonid Schavelev <leonid@iname.com>
wrote:
>Please give me a hint how to build the following
>SQL statement. There are three tables:
>
>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.
>
>Both FIRMS and BANKS have unique identifier fields named ID
>(fields of the SERIAL type). ACCOUNTS contains two references
>to these fields:
>
>ACCOUNTS.F_ID -> FIRMS.ID
>ACCOUNTS.B_ID -> BANKS.ID
>
>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>
>- but exactly the number of firms, which deal with each bank.
>
>Thanks in advance.
>