Hi,

>> > select S1.userid, S1.groupid, S.username
>> > from S
>> > inner join S S1 on
>> > S1.userid=S.userid
>> > where S.groupid='004'

> The primary key is (groupid,userid).

> This is from the IBACCESS plan tab:
>
> PLAN JOIN (S NATURAL,S1 INDEX (PRIMARYKEY_149))

You told there was an index on GroupID, but i hope you don´t meant the PK index, but a separate
index.

As notice by david your query runs a cartesian product this is due how you use the aliases. (This is
fixed in FB2)
Anyway, if you use an alias for a table use them for all tables.

SELECT
S2.userid,
S2.groupid,
S1.username
FROM
S S1
JOIN S S2 ON (S2.userid = S1.userid)
WHERE
S1.groupid = '004'

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info