Eliminating duplicates in SCCM query report

Question

0.00/5 (No votes)

See more:

I get duplicates with the following code, the second part below the dashed line is from a 2nd dataset for a pull down menu. I added the DISTINCT but still have duplicates. Appreciate any help, thanks.

SQL

SELECT DISTINCT
  v_R_System.Name0
  ,v_R_System.AD_Site_Name0
  ,v_R_System.User_Name0
  ,v_R_System.Last_Logon_Timestamp0
  ,v_R_System.Operating_System_Name_and0
  ,v_R_System.CPUType0
  ,v_RA_System_IPAddresses.IP_Addresses0
  ,v_RA_System_MACAddresses.MAC_Addresses0
  ,v_GS_COMPUTER_SYSTEM.NumberOfProcessors0
  ,v_GS_COMPUTER_SYSTEM.SystemType0
  ,v_GS_COMPUTER_SYSTEM.Status0
  ,v_GS_COMPUTER_SYSTEM.Manufacturer0
  ,v_GS_COMPUTER_SYSTEM.Model0
  ,v_GS_SYSTEM_ENCLOSURE.SerialNumber0
  ,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0
  ,v_GS_LastSoftwareScan.LastScanDate
  ,v_GS_WORKSTATION_STATUS.LastHWScan
  ,v_GS_PROCESSOR.NumberOfCores0
  ,v_GS_PROCESSOR.NumberOfLogicalProcessors0
FROM
  v_R_System
  INNER JOIN v_RA_System_IPAddresses
    ON v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID
  INNER JOIN v_RA_System_MACAddresses
    ON v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID
  INNER JOIN v_GS_COMPUTER_SYSTEM
    ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
  INNER JOIN v_GS_SYSTEM_ENCLOSURE
    ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
  INNER JOIN v_GS_X86_PC_MEMORY
    ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
  INNER JOIN v_GS_LastSoftwareScan
    ON v_R_System.ResourceID = v_GS_LastSoftwareScan.ResourceID
  INNER JOIN v_GS_WORKSTATION_STATUS
    ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
  INNER JOIN v_GS_PROCESSOR
    ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID

where

v_R_System.AD_Site_Name0 = @SiteCode
----------------------------------------------------------------------------------------------------------------------------

SELECT distinct
  v_R_System.AD_Site_Name0 as SIteCode
FROM
  v_R_System order by SiteCode

Posted 15-Oct-14 10:55am

Member 11156458

Updated 15-Oct-14 12:09pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 15-Oct-14 18:12pm

By posting such piece of sql query, you explained nothing.
On the other site, you're the owner of data, only you can improve query!
We can't read in your mind or direct from your screen.

Jörgen Andersson 16-Oct-14 1:52am

Since you're using inner joins it's obvious that one of your tables contains duplicate data. Check your table constraints or normalize.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mycroft Holmes · Answer 1 · 2014-10-15T21:43:00

The way I debug this type of problem is to use select *. Comment out the fields and inner joins and do a select * on the base table with the where clause.

note the record count

You might want to add a filter for a duplicated record to reduce the query time.

start uncommenting the inner joins until to get the silly numbers, now you know the problem join, start checking the join field data for duplicates.