Hi,

I'm having a problem with a join - as follows:

I have two ID fields say - ID1 and ID2 on one table

ID1 can be NULL or any positive integer value
ID2 is always a positive integer value

and ID 3 on another table


I need to create a join that will use ID2 for the join condition where ID1 = 0
and then to use ID2 for the join condition where ID1 = NULL
Otherwise ID1 should be used in join (to join to ID3)

I have created the following:
....
LEFT JOIN Contact CC (nolock) ON (AG.ID1 = CC.ID3 AND AG.ID1 <> 0 AND AG.ID1 IS NOT NULL)
OR (AG.ID2 = CC.ID3 AND AG.NTCContactID = 0 OR AG.ID1 IS NULL)

....

This works ok - but it is extremely slow

If anyone knows a better way to do this - to speed it up - please let me know

Thanks