title: SQL Server Table Join issue
body: <p>I am Trying to join three tables and I an getting cross rows data (Getting 6 rows instead of 3). I tried inner,left and right join same result. Could you please help me.</p>
  
  <p><strong>Table 1</strong></p>
  
  <pre><code>SELECT PATIENT,TDACTDATE,TDACTTIME,TDVALUE 
  FROM NurDocumentedResults A 
  WHERE (INTBASE = '400130') 
      AND (TDQUERY = 'NURPTVALUE') 
      AND (TDACTDATE = '20120425') 
  AND TDVALUE &gt;='8.0' AND A.PATIENT='F126' 
  </code></pre>
  
  <p>Result I am getting </p>
  
  <pre><code>F126    20120425    1715    8.8
  F126    20120425    1840    8.4
  F126    20120425    2300    9.7
  </code></pre>
  
  <p><strong>Table 2</strong></p>
  
  <pre><code>SELECT B.PATIENT,A.TESTRESULTDATE,A.TESTRESULTTIME,A.RESULT 
  FROM LabLSpecResultTests A
  INNER JOIN LabLSpecimenFile B 
      ON A.URN=B.URN 
      AND A.TESTRESULTDATE=B.COLLECTIONDATE 
  WHERE A.TESTRESULTDATE='20120425' 
      AND (A.TEST IN ('100.0100', '100.0120', '100.0130', '100.0110')) 
      AND (A.RESULT &gt; '8.0') 
      AND B.PATIENT='F126'
  </code></pre>
  
  <p>Result I am getting</p>
  
  <pre><code>F126    20120425    1756    8.5
  F126    20120425    2335    9.2
  </code></pre>
  
  <p><strong>When I join all three Tables,</strong> </p>
  
  <pre><code>SELECT D.PATIENT,TDACTDATE,TDACTTIME,TDVALUE,A.RESULT,A.RESULTTIME,A.TESTRESULTDATE,A.TESTRESULTTIME
  FROM NurDocumentedResults D 
  LEFT OUTER JOIN LabLSpecimenFile B 
      ON D.PATIENT=B.PATIENT AND D.TDACTDATE=B.COLLECTIONDATE
  INNER JOIN LabLSpecResultTests A 
      ON A.URN=B.URN 
      AND A.TESTRESULTDATE=B.COLLECTIONDATE
  WHERE (INTBASE = '400130') 
      AND (TDQUERY = 'NURPTVALUE') 
      AND (TDACTDATE = '20120425') 
      AND TDVALUE &gt;='8.0' 
      AND A.TESTRESULTDATE='20120425' 
      AND  (A.TEST IN ('100.0100', '100.0120', '100.0130', '100.0110')) 
      AND (A.RESULT &gt; '8.0') 
      AND D.PATIENT='F126'
  </code></pre>
  
  <p><strong>I am getting following Result</strong></p>
  
  <pre><code>F126    20120425    1715    8.8 8.5 2463    20120425    1756
  F126    20120425    1840    8.4 8.5 2463    20120425    1756
  F126    20120425    2300    9.7 8.5 2463    20120425    1756
  F126    20120425    1715    8.8 9.2 22789   20120425    2335
  F126    20120425    1840    8.4 9.2 22789   20120425    2335
  F126    20120425    2300    9.7 9.2 22789   20120425    2335
  </code></pre>