Search code examples
sqljoinsccmwql

Duplicate results from SQL Inner Join (SCCM 2012 WQL)

I am trying to return rows containing items from SCCM 2012 using WQL (which is essentially SQL) and I am getting duplicate rows in my results when I modified it to pull in information from another table using an inner join (Originally I did not care to have the SMS_ObjectContainerItem.ContainerNodeID and simply used the where clause)

Here is the code:

SELECT DISTINCT table1.Name, table1.CollectionID, table2.ContainerNodeID
FROM table1
INNER JOIN table2 ON table1.CollectionID = table2.InstanceKey
WHERE table1.CollectionID IN
(
    SELECT DISTINCT InstanceKey 
    FROM table2
    WHERE ObjectType='5000' AND (ContainerNodeID=16777279 OR ContainerNodeID=16777220)
) AND CollectionType='2' 
ORDER BY table1.Name

One thing I want to note is that the inner query, which feeds the outside WHERE clause, returns 55 results, however the full query returns 84 results, of which all the extras are just duplicates of some of the previous ones. Distinct does not fix this, and I have a feeling the actual fix will be a reworking of the Inner Join/where clause but my SQL skills aren't there yet. Any help would be appreciated to remove the extra rows I am getting.

Thanks.

More information, the query returns no duplicates when I do this, which is the original query I wrote, but now that I want to preserve which ContainerNodeID the row is from, extra rows show up.

SELECT DISTINCT table1.Name, table1.CollectionID
FROM table1
WHERE table1.CollectionID IN
(
    SELECT DISTINCT InstanceKey 
    FROM table2
    WHERE ObjectType='5000' AND (ContainerNodeID=16777279 OR ContainerNodeID=16777220)
) AND CollectionType='2' 
ORDER BY table1.Name

EDITED: Generalized table names to facilitate reading. Added other example

Solution

  • This won't have duplicates but is not a great solution because it does not address the logical issue you have:

    SELECT table1.Name, table1.CollectionID, table2.ContainerNodeID
FROM table1
INNER JOIN table2 ON table1.CollectionID = table2.InstanceKey
WHERE table1.CollectionID IN
(
    SELECT DISTINCT InstanceKey 
    FROM table2
    WHERE ObjectType='5000' AND (ContainerNodeID=16777279 OR ContainerNodeID=16777220)
) AND CollectionType='2' 
ORDER BY table1.Name
GROUP BY table1.Name, table1.CollectionID, table2.ContainerNodeID

    You are getting duplicates because there is more than one row in table2 for a given InstanceKey. Instead what you want is a way to only join to one row in table2 for each instancekey currently all the rows you are the same in both rows so you see it as duplicates. If you had a unique key (in table2) to join to then you would not have the duplicate row issue.