Join Question

[DaveR]

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

[Blade]

I think a better approach might be to join the Contact table on twice rather than joining it on different columns in one go, and then COALESCE'ing the results.

ie.

-- Left join on ID2
LEFT JOIN Contact CC1 (NOLOCK) ON CC1.ID3 = AG.ID1 AND ISNULL(AG.ID1,0) <> 0
LEFT JOIN Contact CC2 (NOLOCK) ON CC2.ID3 = AG.ID2 AND ISNULL(AG.ID1,0) = 0

-- and then everywhere you want Contact referenced, you use the one that isn't null like:
SELECT COALESCE(CC1.ContactName, CC2.ContactName) As ContactName

Hope this helps

edit:
give this a try as well (just uses 1 join):

LEFT JOIN Contact CC (NOLOCK) 
ON CC.ID3 = CASE WHEN ISNULL(AG.ID1,0) = 0 THEN AG.ID2 ELSE AG.ID1 END

[DaveR]

Thanks for that, it makes a huge difference.
I got best performance from the CASE method.

Dave