title: SELECT Statement without duplicate rows on the multiple join tables in SQL Server
body: <p>I have 3 tables that join with together and i want select distinct rows on the B.val3. i use something like this : </p>
  
  <pre><code>SELECT A.val1,A.val2, B.val1, B.val2, B.val3, C.val1, C.val2
  FROM A INNER JOIN
  B ON A.val1 = B.val1 INNER JOIN
  C ON A.val1 = C.val1
  </code></pre>
  
  <p>this statement get to me 24 rows with duplicate B.val3
  now, when i use <code>SELECT DISTINCT A.val1,A.val2, B.val1,...</code>, all 24 records are retrieved, but i want only distinct rows on the B.val3 retrieved (8 rows)</p>
  
  <p>when i use something like : </p>
  
  <pre><code>SELECT DISTINCT A.val1,A.val2, B.val1,
  ...
  C ON A.val1 = C.val1 GROUP BY B.val3
  </code></pre>
  
  <p>i received an error : </p>
  
  <pre><code>Msg 8120, Level 16, State 1, Line 13
  Column 'A.val1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  </code></pre>
  
  <p><strong>EDIT : structure and data</strong></p>
  
  <p>table A as follow : val2 is foreign key of B and val3 is foreign key C</p>
  
  <pre><code>val1   val2    val3
  -----  -----   -----
  1       100     200
  2       100     201
  3       101     200
  4       102     200
  5       102     201
  </code></pre>
  
  <p>table B :</p>
  
  <pre><code>val1   val2    val3
  -----  -----   -----
  100     a2      aaa
  101     b2      bbb
  102     c2      ccc
  </code></pre>
  
  <p>table C :</p>
  
  <pre><code>val1   val2    
  -----  -----   
  200     a3      
  201     b3      
  </code></pre>
  
  <p>and i want get something like :</p>
  
  <pre><code>A.val1  A.val2  A.val3  B.val1  B.val2  B.val3  C.val1  C.val2
  -----   -----   -----   -----   -----   -----   -----   -----
  1       100      200     100      a2     aaa      200    a3
  3       101      200     101      b2     bbb      200    a3      
  4       102      201     102      c2     ccc      201    b3    
  </code></pre>