[Solved] Specific home-made report - Double entries

[soucyinter]

Hello,

I have created this SQL Report this morning and I have double entries. I guess it has something to do with the fact I use TblADUsers and TblComputers but I ain't a 100% sure about it... I know there's a trick with Inner Join or Left join....

I'd say that 85% to 95% of the entries are in double, others are not.
Here it is :
________________

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers
Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername
Inner Join
tblADComputers On tblComputers.Computername = tblADComputers.Computername
Inner Join
tblADusers On tblComputers.Username = tblADusers.Username
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'
_________________

[Hemoco]

TblComputers and tblADUsers must be linked on both the Username and Userdomain fields. Please try the modified report below.

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'

[soucyinter]

Lansweeper wrote:
TblComputers and tblADUsers must be linked on both the Username and Userdomain fields. Please try the modified report below.

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'

After trying your query, it is not working. Still all the duplicated data.

I will use the "distinct" function then.

Thank you for supporting us this fast!
You guys have a great support team.

[soucyinter]

Lansweeper wrote:
TblComputers and tblADUsers must be linked on both the Username and Userdomain fields. Please try the modified report below.

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'

Hello,

I will try your query and see what it does.
I have modified the one I created this morning, I found out that by using "distinct", it clears my double entries (because all rows have the same exact information). Then I cleaned up a few lines of code.

Here it is :

Select Distinct Top 1000000 tblerrors.CFGname, tblComputers.Lastseen,
tblComputers.FQDN, tblComputers.Computer, tblComputers.LastknownIP,
tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'