title: SQL Joining 4 Tables
body: <p>I have to deal with an old Database from my company department.
  We use this DB for hardware managing assignment and tracking.</p>
  
  <p>I'm about to build up a new fronted in c#, as the current MS Access is getting way to slow for the task.</p>
  
  <p>(I'm translated the names of the tables and rows into English, for better understanding)</p>
  
  <ul>
  <li>tbl_hardware</li>
  <li>tbl_hardware_assignment</li>
  <li>tbl_accounts</li>
  <li>tbl_typebradmodel (not important for now, and/or self explanatory)</li>
  </ul>
  
  <p>tbl_hardware
  contains the columns HW_ID, serialnumber, type,model,brand, etc (othe necessary information of the Hardware)</p>
  
  <p>tbl_hardware_assignment
  contains the columns ID, HW_ID (matching with the tbl_hardware.ID), nameID(matching with tbl_accounts.PersID, and since (a int value formated Date [YYYYMMDD] when the entry was created (was not, my idea...))</p>
  
  <p>tbl_account
  contains the columns PersID, Login, etc (other internal information)</p>
  
  <p>This is my current SQL Statement</p>
  
  <pre><code>SELECT tbl_hardware.HW_ID,
         tbl_hardware.Aktiv,
         tbl_hardware.typebradmodelID,
         tbl_type.tabel AS Type,
         tbl_brand.tabel AS Brand,
         tbl_model.tabel AS Model,
         tbl_accounts.Login,
         tbl_hardware_assignment.since
  FROM tbl_hardware
  LEFT OUTER JOIN tbl_typebradmodel ON tbl_hardware.typebradmodelID = tbl_typebradmodel.typebradmodelID
  LEFT OUTER JOIN tbl_type ON tbl_typebradmodel.TypID = tbl_type.TypID
  LEFT OUTER JOIN tbl_brand ON tbl_typebradmodel.MarkeID = tbl_brand.MarkeID
  LEFT OUTER JOIN tbl_model ON tbl_typebradmodel.ModelID = tbl_model.ModelID
  LEFT OUTER JOIN tbl_hardware_assignment ON tbl_hardware.HW_ID = tbl_hardware_assignment.HW_ID
  LEFT OUTER JOIN tbl_accounts ON tbl_hardware_assignment.namenID = tbl_accounts.PersID
  WHERE tbl_hardware.Aktiv = 1 AND 
        tbl_hardware.typebradmodelID in 
        (SELECT tbl_typebradmodel.typebradmodelID
         FROM tbl_typebradmodel
         LEFT OUTER JOIN tbl_type ON tbl_typebradmodel.TypID = tbl_type.TypID
         LEFT OUTER JOIN tbl_brand ON tbl_typebradmodel.MarkeID = tbl_brand.MarkeID
         LEFT OUTER JOIN tbl_model ON tbl_typebradmodel.ModelID = tbl_model.ModelID
         WHERE tbl_typebradmodel.MarkeID = 
               (SELECT tbl_brand.MarkeID 
                FROM tbl_brand 
                WHERE tbl_brand.tabel LIKE 'Samsung')
        ) AND 
        tbl_hardware.HW_ID in 
        (SELECT tbl_hardware_assignment.HW_ID 
         FROM tbl_hardware_assignment, 
              (SELECT MAX(tbl_hardware_assignment.since) AS lastchange,
                      tbl_hardware_assignment.HW_ID
               FROM tbl_hardware_assignment 
               GROUP BY tbl_hardware_assignment.HW_ID) lastentry
         WHERE tbl_hardware_assignment.namenID = 
               (SELECT tbl_accounts.PersID 
                FROM tbl_accounts 
                WHERE tbl_accounts.Login = 'MY_USERNAME') AND 
               tbl_hardware_assignment.HW_ID = lastentry.HW_ID AND
               tbl_hardware_assignment.since = lastentry.lastchange
        )
  </code></pre>
  
  <p>RESULT:</p>
  
  <pre><code>9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER1   20100218
  9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER2   20100218
  10497   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20100810
  10498   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20100810
  10498   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER4   20100819
  10497   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER4   20100819
  10497   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   MY_USERNAME 20120601
  10498   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   MY_USERNAME 20120601
  9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20130502
  9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER5   20130507
  9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20130619
  9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   MY_USERNAME 20130725
  </code></pre>
  
  <p>But I get too many results, AND wrong/multiple user mappings with one hardware.</p>
  
  <p>Any idea where my mistake is?</p>
  
  <p>BTW.: This statement alone returns the correct values</p>
  
  <pre><code>SELECT tbl_hardware_assignment.HW_ID 
  FROM tbl_hardware_assignment, 
       (SELECT MAX(tbl_hardware_assignment.since) AS lastchange, 
               tbl_hardware_assignment.HW_ID
        FROM tbl_hardware_assignment 
        GROUP BY tbl_hardware_assignment.HW_ID) lastentry
  WHERE tbl_hardware_assignment.namenID = 
        (SELECT tbl_accounts.PersID 
         FROM tbl_accounts 
         WHERE tbl_accounts.Login = 'MY_USERNAME') AND 
        tbl_hardware_assignment.HW_ID = lastentry.HW_ID AND 
        tbl_hardware_assignment.since = lastentry.lastchange
  </code></pre>
  
  <p>RESULT:</p>
  
  <pre><code>10497   20120601
  10498   20120601
  11554   20120601
  12353   20120601
  13665   20120918
  13196   20121129
  14616   20130701
  15073   20130705
  9778    20130725
  </code></pre>
  
  <p><em>(As I should not port company stuff outside the office, I hope that I didn't mess up any result or SQL statements.)</em></p>
  
  <p><strong>Here are are more example outputs</strong> <a href="http://pastebin.com/BC06w8Ks" rel="nofollow noreferrer">PASTEBIN</a></p>