title: Sql query join if table result is not null
body: <p>I am trying to get some data from a table and possibly filtering it depending on the result from another query.</p>
  
  <p>The code is something like this:</p>
  
  <pre><code>DECLARE @TestTable  TABLE(TestID INT)
  
  INSERT INTO @TestTable
  SELECT TestID from v_Lookuptable Where SomeID = @input
  
  
  SELECT DISTINCT
  C.[Level],
  C.Name
  FROM
  dbo.CPackage CP 
  Right outer join @TestTable TT on TT.TestID = CP.TestID
  WHERE
  AND (TT.TestID is null OR Exists( Select TT.TestID from @TestTable 
                                      inner join dbo.CProduct CP on CP.TestID = MT.TestID
                                      where TT.TestID = CP.TestID
                                      ))
  </code></pre>
  
  <p>My problem is that if I pass in my currently scenario the <code>@input</code> as 1, then the top part returns some numbers: 1, 2, 3</p>
  
  <p>But if I pass <code>@input</code> as 2, then the top part returns nothing because nothing matches.</p>
  
  <p>The idea is that if nothing matches, return everything in the bottom query, so from <code>dbo.CPackage</code>, else, only return those things where the <code>TestID</code> match.</p>
  
  <p>I have tried various things like changing the joins, case statements, using <code>ISNULL</code> and <code>collasce</code>. Also I have tried to remove the part <code>TT.TestID is null</code> and it then works if the top table returns something, but when I add it again it returns everything and never filters. So I know the logic is wrong... I just can't figure out the correct logic for this.... :S</p>
  
  <p>Is it possible? I presume it is... </p>
  
  <p>Cheers
  Robin</p>
  
  <p>** Solution **</p>
  
  <p>Remove the join and just put it in the where filter...</p>
  
  <pre><code>SELECT DISTINCT
  C.[Level],
  C.Name
  FROM
  dbo.CPackage CP 
  
  WHERE
  CP.TestID = coalesce( (select distinct TestID from v_Lookuptable LT
          where LT.SomeID =  @input), CP.TestID )
  </code></pre>