Re: Help! Very complex SQL statement required.

I don't know anything about Informix (what is a 'SERIAL' type ?), so I don't know if this select will work, but I'll give it a try anyway: SELECT BANKS.ID, COUNT(distinct firms.id) FROM BANKS, FIRMS, ACCOUNTS WHERE BANKS.ID=ACCOUNTS.B_ID AND FIRMS.ID=ACCOUNTS.F_ID GROUP BY BANKS.ID In fact, you don't need the BANKS and ACCOUNTS table, because you have all required data in the Accounts table: select b_id, count(distinct f_id) from accounts group by b_id Marc --------------------------- Leonid Schavelev 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. > --------------------------------------------------------------------------- -- this text added to avoid the error 'more included text than new text' -- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -- this text added to avoid the error 'more included text than new text' -- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -- this text added to avoid the error 'more included text than new text' -- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -- this text added to avoid the error 'more included text than new text' -- ---------------------------------------------------------------------------