>Hi Fabio,
>
>I haven't realized that LEFT JOIN would work just fine. Right now I'm thinking, should I still go with a separate route or 2 additional joins would not affect performance. I don't need these fields for case 1, only for case 2 and I already added a property.
>
performance can degrade,
because LEFT JOIN is not optimized respect a fixed "ON not true".
If LEFT JOIN doesn't increment the result RECCOUNT(),
you can use
AND EXIST(SELECT .... )
into the where condition
>>>Hi Fabio,
>>>
>>>I forgot to mention that join to Groups is derived, e.g. first I have to join to UsGrLink table. I guess, I would probably have to go with two separate paths depending on class property (e.g. I can add some property like lTransferAccounts, that would be true for the second case).
>>>
>>
>>Where is the problem ? LEFT JOIN is ignored if ON return a NULL.
>>Is the problem into the projection fields list ?
>>Post the two versions wished.
>>
>>>>>Hi everybody,
>>>>>
>>>>>I have a queue assignment form that allows to get accounts from UNASSIGNED queue or from a particular investigator.
>>>>>
>>>>>In the first case one of the condition will look like:
>>>>>
>>>>>and Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk IS NULL, in the other case the condition would be
>>>>>Trans.cCommissioned_Owner = m.tcUserID
>>>>>
>>>>>Now, in the second case I'd like to join to another table (Groups) and get some fields from it. How can I make this generic, e.g. my routine doesn't know which type of WHERE condition I'm passing (I'm just passing lcWhereStr) for my select statement? In other words, I want to join with Groups table if cCommission_Owner_UsGrLink_fk is not null, otherwise I want to just put null placeholders instead of the fields.
>>>>>
>>>>>Thanks in advance.
>>>>
>>>>
>>>>...
>>>>LEFT JOIN Groups ON join condition AND cCommission_Owner_UsGrLink_fk IS NOT NULL
>>>>WHERE ....
>>>>
>>>>* PROBABLY
>>>>...
>>>>LEFT JOIN Groups ON Groups.somefield=Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk
>>>>WHERE ...
>>>>