Duplicate results from SQL Inner Join (SCCM 2012 WQL)

Question

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

When you have duplicates, which ones do you want? Sample data and desired results would help clarify what you really want to do. — Gordon Linoff, Jan 06 '16 at 15:03

score 1 · Accepted Answer · answered Jan 06 '16 at 15:36

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.

This is a great solution, and I'm sure it would work for some other cases, but WQL actually doesn't support the GROUP BY clause. The interesting thing is, without the inner join, and removing the request for table2.containerNodeId, this code works fine and only returns the expected number of results. — Marcus, Jan 06 '16 at 15:50
Okay, making this as the solution now because you were right about the table2 having duplicates (I still haven't figured out the reason for this, but I did figure out the filter that I needed to remove the extra duplicates. Adding a check for ObjectType='5000' on the outside query as well as the inside fixed it. — Marcus, Jan 06 '16 at 17:12

Duplicate results from SQL Inner Join (SCCM 2012 WQL)

1 Answers1