title: SCCM SQL Query Join of Multiple tables
body: <p>Here is a Question for all of you SQL brains out there.  I am joining several tables from my SCCM database.  I am trying to join multiple tables.  Here is my query:</p>
  
  <pre><code>SELECT v_R_System.Name0,
    v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,
    v_GS_SYSTEM_ENCLOSURE.Manufacturer0,
    v_GS_SYSTEM_ENCLOSURE.Model0,
    v_R_System.Operating_System_Name_and0,
    v_R_User.User_Name0,
    v_UsersPrimaryMachines.UserResourceID ,
    v_R_User.Full_User_Name0,
    v_R_System.Is_Virtual_Machine0,
    v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,
    v_RA_System_IPAddresses.IP_Addresses0
  FROM v_R_System
  LEFT JOIN v_GS_SYSTEM_ENCLOSURE
  ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
  LEFT JOIN v_RA_System_IPAddresses
  ON v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID
  LEFT JOIN v_GS_X86_PC_MEMORY
  ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
  LEFT JOIN v_UsersPrimaryMachines
  ON v_R_System.ResourceID = v_UsersPrimaryMachines.MachineID
  LEFT JOIN v_R_User
  ON v_UsersPrimaryMachines.UserResourceID = v_R_User.ResourceID
  WHERE (v_RA_System_IPAddresses.IP_Addresses0 NOT LIKE '%:%')
  </code></pre>
  
  <p>In my results, I am getting the same machine listed multiple times for the same computer.  I would expect to see this for every user listed for the computer.  So, the issue I have is that I want all computers to be listed at least once.  Computers that match RecsourceID in the UsersPrimaryMachine table should show for as many primary users are listed that have a matching UserResourceID in the v_R_User table.  I am guessing I need to change a JOIN somewhere to INNER JOIN.  But as soon as I do, I only list the 2 CLIENT machines 2 times each.</p>
  
  <pre><code>Name0   ChassisTypes0   Manufacturer0   Model0  Operating_System_Name_and0  User_Name0  UserResourceID  Full_User_Name0 Is_Virtual_Machine0 TotalPhysicalMemory0    IP_Addresses0
  SCCM-2K12       1   No Enclosure    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    1   4193780 192.168.91.15
  WIN8-CLIENT1    1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.2 (Tablet Edition)   puser   2063597571  Paul User   1   1048052 192.168.91.103
  WIN8-CLIENT1    1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.2 (Tablet Edition)   juser   2063597572  John User   1   1048052 192.168.91.103
  WIN8-CLIENT1    1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.2 (Tablet Edition)   NULL    2080374786  NULL    1   1048052 192.168.91.103
  WIN8-CLIENT1    1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.2 (Tablet Edition)   NULL    2080374787  NULL    1   1048052 192.168.91.103
  WIN8-CLIENT1    1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.2 (Tablet Edition)   NULL    2080374789  NULL    1   1048052 192.168.91.103
  WIN8-CLIENT1    1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.2 (Tablet Edition)   NULL    2080374790  NULL    1   1048052 192.168.91.103
  WIN81-CLIENT2   1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.3 (Tablet Edition)   jdoe    2063597570  Jane Doe    1   2096628 192.168.91.100
  WIN81-CLIENT2   1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.3 (Tablet Edition)   puser   2063597571  Paul User   1   2096628 192.168.91.100
  WIN81-CLIENT2   1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.3 (Tablet Edition)   NULL    2080374786  NULL    1   2096628 192.168.91.100
  WIN81-CLIENT2   1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.3 (Tablet Edition)   NULL    2080374787  NULL    1   2096628 192.168.91.100
  WIN81-CLIENT2   1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.3 (Tablet Edition)   NULL    2080374789  NULL    1   2096628 192.168.91.100
  WIN81-CLIENT2   1   No Enclosure    NULL    Microsoft Windows NT Workstation 6.3 (Tablet Edition)   NULL    2080374790  NULL    1   2096628 192.168.91.100
  SCSM-2K12   1   No Enclosure    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    1   4193780 192.168.91.12
  SCSM-SSP    1   No Enclosure    NULL    Microsoft Windows NT Server 6.1 NULL    NULL    NULL    1   2096632 192.168.91.17
  MAIL    1   No Enclosure    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    1   6290932 192.168.91.11
  SCOM-2K12-DB    1   No Enclosure    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    1   4193780 192.168.91.19
  SCOM-2K12   1   No Enclosure    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    1   2096628 192.168.91.18
  SCORCH-2K12 1   No Enclosure    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    1   2096628 192.168.91.21
  SCDW-2K12   NULL    NULL    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    NULL    NULL    192.168.91.13
  SHARE-2K8   NULL    NULL    NULL    Microsoft Windows NT Server 6.1 NULL    NULL    NULL    NULL    NULL    192.168.91.16
  IIS-2K12    NULL    NULL    NULL    Microsoft Windows NT Server 6.2 NULL    NULL    NULL    NULL    NULL    192.168.91.14
  </code></pre>
  
  <p>Whoever can figure this out would be my hero!</p>