Query has a few duplicates

[Ponchohobono]

When I run the query below I get a few computers duplcated. It appears that there are multiple entries for a few computers in the tblSystemEnclosure table. I'd also like to get the OS in there as well. Any suggestions?

SELECT tblCompCustom.Location,
TsysChassisTypes.ChassisName as Type,
tblcomputers.Computer,
tblComputerSystemProduct.Vendor as Vendor,
tblComputerSystemProduct.Name as Model,
tblComputerSystemProduct.IdentifyingNumber as SerialNumber,
tblcomputers.LastknownIP as IPAddress,
tblcomputers.LastActiveScan
FROM tblcomputers
LEFT JOIN tblCompCustom ON tblCompCustom.Computername = tblcomputers.Computername
LEFT JOIN tblSystemEnclosure ON tblSystemEnclosure.Computername = tblcomputers.Computername
LEFT JOIN tblComputerSystemProduct ON tblComputerSystemProduct.Computername = tblcomputers.Computername
LEFT JOIN TsysChassisTypes ON tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
ORDER BY tblcomputers.Computer

[Ponchohobono · Accepted solution]

Ahhh. This worked for me.

Select tblCompCustom.Location As Location,
       TsysChassisTypes.ChassisName As Type,
       tblcomputers.Computer As Name,
       Web40OSName.OSname As OS,
       Web40OSName.SP As SP,
       tblComputerSystemProduct.Vendor As Vendor,
       tblComputerSystemProduct.Name As Model,
       tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
       tblcomputers.LastknownIP As IPAddress,
       tblcomputers.LastActiveScan As LastSeen,
       tblcomputers.Computername
From tblcomputers
Left Join tblCompCustom On tblCompCustom.Computername = tblcomputers.Computername
Left Join tblSystemEnclosure On tblSystemEnclosure.Computername = tblcomputers.Computername
Left Join tblComputerSystemProduct On tblComputerSystemProduct.Computername = tblcomputers.Computername
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join Web40OSName On Web40OSName.Computername = tblcomputers.Computername
Where TsysChassisTypes.Chassistype <> 12
UNION
SELECT tblCustDevices.Location As Location,
       tsysDevicetypes.ItemTypename As Type,
       tblCustDevices.Displayname As Name,
       tblCustDevices.Description As OS,
       CAST('' As int) As SP,
       tblCustDevices.Vendor As Vendor,
       tblCustDevices.Model As Model,
       tblCustDevices.Serialnumber As SerialNumber,
       tblCustDevices.Ipaddress As IPAddress,
       tblCustDevices.LastSeen As LastSeen,
       CAST('' As int) As Computername
FROM tblCustDevices, tsysDevicetypes
WHERE tblCustDevices.Devicetype = tsysDevicetypes.ItemType
Order By Name

[Hemoco]

Fields that are numeric in the first query must also be numeric in the 2e Query (computername, sp, ...)

Make them numeric in the 2e query or varchar in the first.

[Ponchohobono]

That worked thanks. One more tweak. I want to use a UNION to also return devices. Using the two queries seperatly they work but using a UNION I get an error that says, "Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric." What am I doing wrong?

Select tblCompCustom.Location As Location,
       TsysChassisTypes.ChassisName As Type,
       tblcomputers.Computer As Name,
       Web40OSName.OSname As OS,
       Web40OSName.SP As SP,
       tblComputerSystemProduct.Vendor As Vendor,
       tblComputerSystemProduct.Name As Model,
       tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
       tblcomputers.LastknownIP As IPAddress,
       tblcomputers.LastActiveScan As LastSeen,
       tblcomputers.Computername
From tblcomputers
Left Join tblCompCustom On tblCompCustom.Computername = tblcomputers.Computername
Left Join tblSystemEnclosure On tblSystemEnclosure.Computername = tblcomputers.Computername
Left Join tblComputerSystemProduct On tblComputerSystemProduct.Computername = tblcomputers.Computername
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join Web40OSName On Web40OSName.Computername = tblcomputers.Computername
Where TsysChassisTypes.Chassistype <> 12
UNION
SELECT tblCustDevices.Location As Location,
       tsysDevicetypes.ItemTypename As Type,
       tblCustDevices.Displayname As Name,
       tblCustDevices.Description As OS,
       '' As SP,
       tblCustDevices.Vendor As Vendor,
       tblCustDevices.Model As Model,
       tblCustDevices.Serialnumber As SerialNumber,
       tblCustDevices.Ipaddress As IPAddress,
       tblCustDevices.LastSeen As LastSeen,
       '' As Computername
FROM tblCustDevices, tsysDevicetypes
WHERE tblCustDevices.Devicetype = tsysDevicetypes.ItemType
Order By Name

[Hemoco]

try this:
I've filtered out the docking stations.

Select tblCompCustom.Location, TsysChassisTypes.ChassisName As Type,
  tblcomputers.Computer, Web40OSName.OSname, Web40OSName.SP,
  tblComputerSystemProduct.Vendor As Vendor, tblComputerSystemProduct.Name As
  Model, tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
  tblcomputers.LastknownIP As IPAddress, tblcomputers.LastActiveScan,
  tblcomputers.Computername
From tblcomputers Left Join                             
  tblCompCustom On tblCompCustom.Computername = tblcomputers.Computername
  Left Join
  tblSystemEnclosure On tblSystemEnclosure.Computername =
    tblcomputers.Computername Left Join
  tblComputerSystemProduct On tblComputerSystemProduct.Computername =
    tblcomputers.Computername Left Join
  TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
    TsysChassisTypes.Chassistype Inner Join
  Web40OSName On Web40OSName.Computername = tblcomputers.Computername
Where TsysChassisTypes.Chassistype <> 12
Order By tblcomputers.Computer