title: SQL query with 5 tables using joins
body: <p>I have a very complex SQL server database that I have attempted to simplify here.  I have 5 tables that I would like to pull information from.  I want all records from Table_A.  The other tables may or may not have records in them.  when they do I want that information added to my final table.  I can't get query to return correct values.  I've tried different joins, etc but keep getting the same values.</p>
  
  <p><strong>Table_A</strong>:</p>
  
  <pre><code>LotID     MaterialNumber        RejectReason   
  1234      987                   16
  2345      876   
  3456      987
  4567      876
  5678      987
  6789      987                   15
  7890      987           
  8901      987
  </code></pre>
  
  <p><strong>Table_B</strong>:</p>
  
  <pre><code>LotID     Color  
  1234      0.25
  5678      0.28
  6789      0.27
  8901      0.3
  </code></pre>
  
  <p><strong>Table_C</strong>:</p>
  
  <pre><code>LotID     Gloss  
  1234      1.25
  2345      1.28
  6789      1.27
  8901      1.30
  </code></pre>
  
  <p><strong>Table_D</strong>:</p>
  
  <pre><code>LotID   Thickness  
  2345    2.25
  5678    2.28
  6789    2.27
  7890    2.3
  </code></pre>
  
  <p><strong>Table_E</strong>:</p>
  
  <pre><code>NR      Description
  12      Tear
  13      Run
  14      Drip
  15      Wet
  16      Dry
  </code></pre>
  
  <p><strong>Table_F</strong>:</p>
  
  <pre><code>Material_Number Part_Descrip
  
  987         Front
  876         Rear
  </code></pre>
  
  <p>Query</p>
  
  <pre><code>Select
      Table_A.LotID, Table_A.MaterialNumber,
      Table_B.Color,
      Table_C.Gloss,
      Table_D.Thickness
      Table_E.Description,
      Table_F.Part_Descrip,
  FROM 
      Table_A A
  LEFT OUTER JOIN
      Table_B B ON A.LotID = B.LotID
  LEFT OUTER JOIN
      Table_C C ON A.LotID = C.LotID
  LEFT OUTER JOIN
      Table_D D ON A.LotID = D.LotID
  LEFT OUTER JOIN
      Table_E E ON A.RejectReason = E.NR
  LEFT OUTER JOIN
      Table_F F ON A.MaterialNumber = F.Material_Number
  ORDER BY 
      A.LotID
  </code></pre>
  
  <p>Returning:</p>
  
  <pre><code>LotID    Material Number    Color   Gloss   Thickness   Reject  Part Description
  6789     987                0.27    1.28    2.27        Wet     Front
  </code></pre>
  
  <p>Want it to return:</p>
  
  <pre><code>LotID   Material Number Color   Gloss   Thickness   Reject  Part Description
  1234    987             0.25    1.25    Null        Dry     Front
  2345    876             Null    1.28    2.25        Null    Rear
  3456    987             Null    Null    Null        Null    Front
  4567    876             Null    Null    Null        Null    Rear
  5678    987             0.28    1.28    2.28        Null    Front
  6789    987             0.27    1.28    2.27        Wet     Front
  7890    987             Null    Null    2.3         Null    Front
  8901    987             0.3     1.3     Null        Null    Front
  </code></pre>
  
  <p>ORIGINAL Query</p>
  
  <pre><code>SELECT
  Z_LINE5_QU.CollectTime,
  Z_LINE5_QU.LotID, 
  Z_LINE5_QU.MOULD_ID, 
  Z_LINE5_QU.CAV,
  Z_LINE5_QU.MOULD_CAV,
  Z_LINE5_QU.OK, 
  Z_LINE5_QU.NOK, 
  Z_LINE5_QU.OKAFTERREWORK, 
  Z_LINE5_QU.NOKAFTERREWORK, 
  Z_LINE5_QU.SENDTOREWORK, 
  Z_LINE5_QU.SPOTCH_OK, 
  Z_LINE5_QU.SPOTCH_NOK, 
  Z_LINE5_QU.MOULD_CY, 
  Z_LINE5_QU.LINE, 
  Z_LINE5_QU.LINE_ID, 
  Z_LINE5_QU.REASON_NR1, 
  Z_CODE_QU_REASON.DESCR, 
  Z_CODE_PART.PART_DESCR,
  Z_LINE5_SPOTCH_GLOSS.GLOSS_MP1, 
  Z_LINE5_SPOTCH_GLOSS.GLOSS_MP2, 
  Z_LINE5_SPOTCH_THICK.THICK_Z1, 
  Z_LINE5_SPOTCH_THICK.THICK_Z2, 
  Z_LINE5_SPOTCH_THICK.THICK_Z3, 
  Z_LINE5_SPOTCH_THICK.THICK_Z4, 
  Z_LINE5_SPOTCH_THICK.THICK_Z5, 
  Z_LINE5_SPOTCH_THICK.THICK_Z6, 
  Z_LINE5_SPOTCH_THICK.THICK_Z7, 
  Z_LINE5_SPOTCH_THICK.THICK_Z8, 
  Z_LINE5_SPOTCH_THICK.THICK_Z9
  
  FROM RTUSPRAY.DBO.Z_LINE5_QU Z_LINE5_QU
  
          LEFT OUTER JOIN RTUSPRAY.DBO.Z_LINE5_SPOTCH_COLOR
                ON Z_LINE5_QU.LOTID = Z_LINE5_SPOTCH_COLOR.LOTID
  
          LEFT OUTER JOIN RTUSPRAY.DBO.Z_LINE5_SPOTCH_THICK
                ON Z_LINE5_QU.LOTID=Z_LINE5_SPOTCH_THICK.LOTID
  
          LEFT OUTER JOIN RTUSPRAY.DBO.Z_LINE5_SPOTCH_GLOSS
                ON Z_LINE5_QU.LOTID=Z_LINE5_SPOTCH_GLOSS.LOTID
  
          LEFT OUTER JOIN RTUSPRAY.DBO.Z_CODE_QU_REASON
                ON Z_LINE5_QU.REASON_NR1=Z_CODE_QU_REASON.NR
  
          LEFT OUTER JOIN RTUSPRAY.DBO.Z_CODE_PART
                ON Z_LINE5_QU.CODE_SAP = Z_CODE_PART.CODE_SAP_PRESER
  
  ORDER BY Z_LINE5_QU.LotID
  </code></pre>