Avoiding duplicate assets when doing a left-join to AD users

[Jeff_Henze]

I’m trying to do an asset change report showing the recently assigned assets (I put info in custom1..custom6). I store the assignee’s employee number in Custom1; but since that’s not the most descriptive information, I do a left-join on the tblAdusers to show me the name (displayname). This causes a small problem: when one employee number has multiple IDs in AD, it creates a duplicate line of assets, one for each ID. I would like it to only show one line, selecting whichever line from AD for the name I don’t really care, but only one. Here’s the report:

Select Top 1000000 tblAssets.AssetName,
tblAssets.Domain,
Cast(tblAssetCustom.Custom4 As datetime) As [Date Assigned],
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
tblAssets.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom1 As [Assigned Employee #],
tblADusers1.Displayname As [Assigned Employee],
tblAssetCustom.Custom2 As Assignment,
tblAssetCustom.Custom5 As Technician,
tblAssetCustom.Custom6 As PO,
tblAssetCustom.Lastchanged,
tblADusers.EmployeeNumber As [Last On Emp#],
tblADusers.Username As [Last On ID],
tblADusers.Displayname As [Last On Name]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join tblADusers tblADusers1 On tblAssetCustom.Custom1 =
tblADusers1.EmployeeNumber
Where (tblAssetCustom.Custom3 Like 'NoCo' And tblAssetCustom.Custom1 <> ''
And tblAssetCustom.State = 1) Or
(tblAssetCustom.Custom2 <> '') Or
(tblAssetCustom.Custom5 <> '')
Order By tblAssets.Domain,
[Date Assigned] Desc

Here’s an example of the output, showing how it duplicates that last asset twice because an employee with that number has multiple AD accounts – one as “Doc Smith” and one as “Doctor Smith” both having the same employee ID:

AssetName | Domain | Date Assigned | Serialnumber | Model | Lastseen | Company | Assigned Employee # | Assigned Employee
DESKTOP68D01 | nasa | 01/01/2016 | a12bc32 | OptiPlex 790 | 07/25/2016 16:41:08 NoCo | 123456 | Doe, John
LAPTOP10D01 | nasa | 01/01/2016 | 324klnl | OptiPlex 7010 | 08/01/2016 15:16:14 NoCo | 45678 | Smith, Doc
LAPTOP10D01 | nasa | 01/01/2016 | 324klnl | OptiPlex 7010 | 08/01/2016 15:16:14 NoCo | 45678 | Smith, Doctor

I’d like it to only show one entry per asset. If it grabs the whatever is the first assigned employee display name it finds, that would be fine. But I’d rather not have two of the same asset returned each with its unique AD account attached to it.

Any ideas on how to achieve that?

[Jeff_Henze]

I forgot about the differences in language (not that I'd recognize them anyway at this point) - I reran it and it ran perfect this time. I noted that my 'where and/or' statement was not working as I hoped so I simplified it. Just in case anyone reads this in the future, the below has your changes and include my simplified where statement.

Thanks for all the help MikeMc!

-Jeff

Select Top 1000000 tblAssets.AssetName,
tblAssets.Domain,
Cast(tblAssetCustom.Custom4 As DATETIME) As [Date Assigned],
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
tblAssets.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom1 As [Assigned Employee #],
T1.Displayname As [Assigned Employee],
tblAssetCustom.Custom2 As Assignment,
tblAssetCustom.Custom5 As Technician,
tblAssetCustom.Custom6 As PO,
tblAssetCustom.Lastchanged,
tblADusers.EmployeeNumber As [Last On Emp#],
tblADusers.Username As [Last On ID],
tblADusers.Displayname As [Last On Name]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join (Select adu.EmployeeID,
Max(adu.Displayname) As Displayname
From tblADusers adu
Group By adu.EmployeeID) T1 On T1.EmployeeID = tblAssetCustom.Custom1
Where tblAssetCustom.Custom3 Like 'NoCo' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
[Date Assigned] Desc

[MikeMc]

Jeff,

The error happens because you are running SQL CE with your Lansweeper DB. The query options available in that version versus full SQL Server are very limited. My updated query should work with CE and give you the result you want.

[Jeff_Henze]

Ran it - I get an error now though
"There was an error parsing the query. [ Token line number = 1,Token line offset = 295,Token in error = Select ]"

This went beyond my SQL skills many revisions ago, so I'm not seeing the error but I'm sure it's incredibly obvious to others. Can you take another look?

[MikeMc]

I see the mistake I made in my original query. I didn't link back the employee id correctly. I updated my original query and changed the assigned employee field to a sub-query to retrieve the top display name linked from the original query.

[Jeff_Henze]

<edit>

Thanks - it works *except* that I no longer see any info under "Assigned Employee". I'm trying to figure out why that is the case. All the other stuff seems to work great - no duplicates anymore.

[MikeMc · Accepted solution]

I tweaked your left join to return the first top result, but I didn't have a chance to really test it out.

Select Top 1000000 tblAssets.AssetName,
  tblAssets.Domain,
  Cast(tblAssetCustom.Custom4 As DATETIME) As [Date Assigned],
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Model,
  tblAssets.AssetID,
  tblAssets.Lastseen,
  tblAssetCustom.Custom3 As Company,
  tblAssetCustom.Custom1 As [Assigned Employee #],
  T1.Displayname As [Assigned Employee],
  tblAssetCustom.Custom2 As Assignment,
  tblAssetCustom.Custom5 As Technician,
  tblAssetCustom.Custom6 As PO,
  tblAssetCustom.Lastchanged,
  tblADusers.EmployeeNumber As [Last On Emp#],
  tblADusers.Username As [Last On ID],
  tblADusers.Displayname As [Last On Name]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblADusers On tblAssets.Username = tblADusers.Username
  Left Join (Select adu.EmployeeID,
    Max(adu.Displayname) As Displayname
  From tblADusers adu
  Group By adu.EmployeeID) T1 On T1.EmployeeID = tblAssetCustom.Custom1
Where (tblAssetCustom.Custom3 Like 'NoCo' And tblAssetCustom.Custom1 <> '' And
  tblAssetCustom.State = 1) Or
  (tblAssetCustom.Custom2 <> '') Or
  (tblAssetCustom.Custom5 <> '')
Order By tblAssets.Domain,
  [Date Assigned] Desc