[i]<See Vic's note in the Announcements Section, regarding missing posts...>
[/i]
I think my previous post looked something like this;
SELECT OBJECT.OBJECT_ID,
OBJECT.OBJECT_NAME,
OBJECT.PRESENTATION_NAME,
OBJ_TO_OBJ.PARENT_ID,
OBJ_TO_OBJ.CHILD_ID,
OBJECT2.OBJECT_ID,
OBJECT2.PRESENTATION_NAME,
OBJECT.OBJECT_TYPE_ID,
OBJECT2.OBJECT_TYPE_ID
FROM [color=blue]DBNAME[/color].OBJECT, MART1.OBJ_TO_OBJ, [color=blue]DBNAME[/color].OBJECT OBJECT2
WHERE (OBJECT.OBJECT_ID = OBJ_TO_OBJ.PARENT_ID)
AND (OBJECT2.OBJECT_ID = OBJ_TO_OBJ.CHILD_ID)
AND (OBJ_TO_OBJ.DELETE_TIME IS NULL)
AND (OBJECT.PRESENTATION_NAME = [color=blue]'AGENT_GROUP_NAME'[/color])
AND (OBJECT.OBJECT_TYPE_ID = 2)
AND (OBJECT2.OBJECT_TYPE_ID = 0)
NOTES:
1. Items in [color=blue]BLUE [/color] are local variables.
2. I have opted to apply just one "IS NULL" to the Delete Time of the Obj_to_Obj Table, which seems to work very well and removes duplcation, too...
3. I have constrained both versions of the Object Table - where it is the ParentID, to Object_Type_ID "2" (Agent Group) and where it is the ChildID, to Object_Type_ID "0" (Agent). This speeds up the Query by Flitering just the required Object Types, prior to the other WHERE clauses from being conducted.
I did put in some other detail in my (lost) post but I'm getting old and I can't remember what that might have been, now...
I hope this helps - please let me know if it works for you..?
Tony
