> > If I modify the SQL to be:
> >
> > SELECT DISTINCT
> > USERS_GROUP_LINK.UGL_USERS_ID,
> > USERS.USERS_NAME,
> > USERS.USERS_ID // This is the only difference to the original SQL
> > FROM
> > USERS_GROUP_LINK
> > INNER JOIN
> > USERS
> > ON
> > USERS_GROUP_LINK.UGL_USERS_ID=USERS.USERS_ID
> > AND
> > USERS_GROUP_LINK.UGL_UG_ID=2
> >
> > The problem goes away. Why????
>
> From that I would assume that USERS_ID is the primary key on USERS?
> I presume that that DISTINCT should not actually be necessary - there
> should only be one UGL_USERS_ID for each user in a UGL_UG_ID ( Primary
> key on USERS_GROUP_LINK should be 'UGL_UG_ID, UGL_USERS_ID' )
> What happens if you change the last lines to -
> WHERE USERS_GROUP_LINK.UGL_UG_ID=2
>
> I am probably wrong, but I tend to do this sort of query with a sub select
>
> SELECT
> USERS_GROUP_LINK.UGL_USERS_ID,
> ( SELECT USERS_NAME FROM USERS
> WHERE USERS_ID = USERS_GROUP_LINK.UGL_USERS_ID ) AS USERS_NAME
> FROM USERS_GROUP_LINK
> WHERE USERS_GROUP_LINK.UGL_UG_ID=2
> ORDER BY 2
>
> ( That just gives you alphabetical order on the names )
> That way you know exactly what you are getting, and from where
>
> I think that you have created an INNER JOIN that requires the USERS_ID
> simply to identify the records, and then IBO needs that visible to allow
> it to update buffers.

Hi Lester,

You are 100% correct with the DISTINCT not required, not sure How it made it
into the post, as it is not part of the original SQL.

You assumption regarding PK on the USERS table, as well as on the
USERS_GROUP_LINK table is once more 100% correct.

I originally had WHERE and not AND, but it would not work, I have just tried
once more, and it works, so I am not sure what I may have change in the mean
time, or if it just needed to be "build" rather than "make"

I normally test the SQL using something like IBExpert, to guaranty that it
is correct as well as to make the call as efficient as possible, by
analyzing the stats on the compiled SQL, in this case, the SQL does not
require the USERS.USERS_ID to return the correct subset of data, but as you
said, IB appears to require this field for some reason, which I am not able
to discover. Hopefully the more I work with IB the more familiar I will
become with its needs ;-)

Once more thank you

daniel.