I'm thinking, that it would be much better to have some indicator on the Trans_Employees_Queues records, that we already assigned the corresponding Trans to FORCED queue. We can either have iForced_Queue flag or try to deactivate the records, e.g. set iActive_Flag to 0 and then additional process would re-activate them once the FORCED queue expires. I believe it would be much simpler than try to build such complicated query with NOT IN condition. I proposed this idea to the group, let see, what would be response.

>>Hi everybody,
>>
>>Here is my present code
>

>>text TO lcSQL TEXTMERGE NOSHOW PRETEXT 7
>>			trans.*, ;
>>			Trans_Employees_Queues.* ;
>>			from trans INNER join Trans_Employees_Queues ;
>>			on trans.cTrans_pk = Trans_Employees_Queues.cTrans_fk ;
>>			<<m.tcJoin>> ;
>>			where trans.cDepartment_Code like '<<this.cDepartment_code>>' ;
>>			and trans.cResolution_Codes_fk is NULL
>>	ENDTEXT
>>
>>	lcSQL = "SELECT " + IIF(EMPTY(m.tnTotalNumber),"", ;
>
>* if tnTotalNumber exists on the sql scope you can:
>
>>		"TOP m.tnTotalNumber ")+ ;
>>		m.lcSQL + iif(not empty(m.tcWhere), ;
>>		" AND " + m.tcWhere,"") + ;
>>		" order by trans.tDate_Received"
>

>>
>>
>>However, there is a problem. There could be records in Trans_Employees_Queues for "FORCED" queue. In other words, for these cases we create additional records in Trans_Employees_Queues table and then use Employee_Queue_Schedules table to assign them. Now, obviously I do not want to assign records which are already in the FORCED queue. Do you see a way to amend this query?
>>
>>Thanks a lot in advance.
>>
>>In other words, I need the same query, but with
>>
>>and Trans.cTrans_fk not in (select cTrans_pk from Trans inner join Trans_Employees_Queues on ... inner join ...
>>)
>>
>>
>>But the inner select should satisfy the same conditions as my outer select...
>
>- post the complete selects, for give a good and sure solution
>
>Hovewer, for do this you have to build a complete work table, and filter it.
>REMEMBER THIS:
>you can transform the "not in" or "not exists" condition in an INNER JOIN,
>into a LEFT JOIN with a WHERE IS NULL condition.
>

>select
>...
>from trans T INNER join Trans_Employees_Queues Q on T.cTrans_pk = Q.cTrans_fk
>INNER JOIN ....
>LEFT JOIN Employee_Queue_Schedules S ON S.FK=
>WHERE .... and S.FK IS NULL
>
>