Thanks for your reply as always Sergey. However, I must of not been very clear in my problem.

I know about using the OUTER JOIN but in this particular case I either only want to pull all "or" pull only those that match depending upon if lookup table contains any records to do a JOIN. Using OUTER JOIN will "always" pull all from one table and this is not what I want.

>John,
>
>You can use LEFT OUTER JOIN to get all records from the table on the left and matching records from the table on the right or columns with Nulls if there's no matching record.

SELECT * FROM tableA
>    LEFT JOIN tableB ON tableA.ID=@tableB.ID
>    LEFT JOIN tableC ON tableA.ID2=@tableC.ID
>    LEFT JOIN tableD ON tableA.ID3=@tableD.ID
>

>
>
>>I though this wouldn't be too difficult but I can't find an elegant solution to my situation. I need to query a table so that if there is record found in a "lookup" table to use JOIN on that table. If no record is found, don't do a table JOIN (pull all records). My current query is:
>>

>>SELECT @CampTot = COUNT(*) FROM tableB WHERE ID=@ID
>>IF @CampTot = 0
>>   SELECT * FROM tableA
>>ELSE
>>   SELECT * FROM tableA JOIN tableB ON tableA.ID=@tableB.ID
>>...
>>

>>
>>Above very simple sample works but I have to check not one but three tables and checking for different combinations with lots of IF...ELSE scatter all over the place with duplicate but only slightly different SELECT statements is a big mess. TIA.