Subject RE: [IBO] Help with setting up the master-detail relationships - please
Author Daniel R. Jimenez
[snip]

> IBQMaster =
> SELECT UG_NAME, UG_ID FROM USERS_GROUP
>
> IBQMember =
> SELECT USERS_NAME, USERS_ID
> FROM USERS
> WHERE USERS.USERS_ID IN
> ( SELECT UGL_USERS_ID FROM USERS_GROUP_LINK
> WHERE UGL_UG_ID = :GROUP )
>
> IBQNotMember =
> SELECT USERS_NAME, USERS_ID
> FROM USERS
> WHERE USERS.USERS_ID NOT IN
> ( SELECT UGL_USERS_ID FROM USERS_GROUP_LINK
> WHERE UGL_UG_ID = :GROUP )
>
> Keylinks are *_ID
> Link second query :GROUP to UG_ID of first query MasterParamLinks ->
> GROUP=UG_ID with MasterSource the IBQMaster Datasource
>
> Just 'JOIN' any other information you need to the above basic queries
>
> --
> Lester Caine

Hi Lester,

Thank you for your help.

I was just putting an email together to say I have found the solution when
your reply arrived.

I had the query for the IBQMaster sorted and working, the same can be said
about the IBQMember. I had created and SQL for the IBQNotMember, which was
providing the correct records, the problem was I had incorrectly setup the
links within the Query component of IBObjects.

This is my solution:

SELECT
USERS.USERS_NAME,
USERS.USERS_ID
FROM
USERS_GROUP_LINK
INNER JOIN
USERS_GROUP
ON
USERS_GROUP_LINK.UGL_UG_ID = :UG_ID
RIGHT JOIN
USERS
ON
USERS_GROUP_LINK.UGL_USERS_ID = USERS.USERS_ID
WHERE
USERS_GROUP_LINK.UGL_USERS_ID IS NULL

In the IBO Query component for the IBQNotMember I also add the following to
the MasterParamLinks:

UG_ID=USERS_GROUP.UG_ID

If my solution is not the correct way or it is not very efficient, could you
please let me know, as I have notice that your solution is quite different,
on the SQL side and on the way you setup the Master-Details links within the
IBO Query.

Once more thank you for your help

daniel