RE: Help! Very complex SQL statement required.

Hey, It's a trick ... If you remove the "where" and "and" statements and the related tables (firms,accounts) in your sql you get the same result. select b_id, count(*) from banks group by b_id; so then you change the count to do what you want select b_id, count(unique f_id) from banks group by b_id; Give it a go.. Cheers, Tony. ---------- From: Leonid Schavelev To: informix-list@rmy.emory.edu Subject: Help! Very complex SQL statement required. Date: Thursday, 14 August 1997 17:48 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. -- Leonid Schavelev, RUSSIA, Ivanovo State Power University MailTo:Leonid@iname.com (MailTo:Leonid@polytech.ivanovo.su) http://www.polytech.ivanovo.su/~leonid