title: Very complicated TSQL with INNER JOIN the same table twice
body: <p>Let's say I have 2 tables (in fact there are more in this command, but only those affect question).</p>
  
  <p>Table A have unique Accounts and Table C have Unique Materials.</p>
  
  <p>Now lets say Unique Account with ID 11 can have few Unique Materials (ex.: 10, 18, 19, 24).</p>
  
  <p>Materials can be of the same type or different (ex.: 10 - type2, 18 - type2, 19 type1, 24 type3) and they have their numbers (ex.: 10 - A24, 18 - A25, 19 - A24, 24 - B55) and many other values (but for example 1 is enough).</p>
  
  <p>Every Material have id of Account in dedicated column.</p>
  
  <p>Now I need to select some of the values into DataTable based on the input Strings criteria:</p>
  
  <pre><code>String1 = Afst; //some value from tableA
  //...
  String2 = Ctype; // SomeType of Material from table C
  String3 = dn; // number of Material from table C
  </code></pre>
  
  <p>Afst (tableA) |...| dn (tableC number of Ctype) | dn2 (table C number of Type2)</p>
  
  <p>Where dn2 have specified type.</p>
  
  <p>The problem here is that I need to select all materials with Ctype even if Type2 doesn't exist for this Account.</p>
  
  <p>For example this will give all results when both Ctype and Type2 exists in :</p>
  
  <pre><code>("SELECT a.id, a.Afst, c.dn, d.dn as 'dn2' FROM sometableA a INNER JOIN sometableC c ON a.id=c.idofa INNER JOIN sometableC d ON a.id=d.idofa WHERE a.Afst like '%" + String1 + "%' and c.MaterialType='" + String2 + "' and c.dn like '%" + String3 + "%' and d.MaterialType='2'");
  </code></pre>
  
  <p>Notice: I'm not allowed to change database tables in any way, so I can't split Material table into tables by type.</p>
  
  <p>This should also be within single TSQL command.</p>