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.