We help IT Professionals succeed at work.

# How to find duplicate items in a self-referential table?

on
I am trying to find which items in the column Parent have more than one occurance and do not appear at all in the column GrandParent, however I can't seem to get it.

I would like to return the columns Parent and GrandParent with the following criteria.
Parents that do not have a GrandParent and occur more than once in the ParentTable column.

Fake Data
Parent      GrandParent
1                 NULL
2                 1
3                 NULL
3                 NULL

Desired Result
- Exclude the row with the number 1 because it has children.
- Only return 3 because it occurs twice with no parent number.

Number      NumofOccur
3                 2

``````Code That Does Not Work

SELECT A.PARENT, COUNT(PARENT) NDANOCOUNT
From TABLETEST A
WHERE A.GRANDPARENT IS NOT NULL AND A.PARENT NOT IN

(

SELECT B.GRANDPARENT
FROM TABLETEST B
WHERE B.GRANDPARENT IS NOT NULL

)

GROUP BY A.PARENT
HAVING COUNT(PARENT) > 1
ORDER BY COUNT(PARENT) DESC
``````
Comment
Watch Question

## View Solution Only

Commented:

The only Grandparent is excluded by the first condition WHERE A.GRANDPARENT IS NOT NULL

Cheers, Andrew
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
SELECT   PARENT, COUNT(*) cnt
FROM t t1
WHERE grandparent IS NULL AND NOT EXISTS(SELECT NULL
FROM t t2
WHERE t2.grandparent = t1.PARENT)
GROUP BY PARENT

Commented:
As stated the check against grandparent is not required.
Cheers, Andrew
``````SELECT A.PARENT, COUNT(PARENT) NDANOCOUNT
From TABLETEST A
WHERE  A.PARENT NOT IN
(
SELECT B.GRANDPARENT
FROM TABLETEST B
WHERE B.GRANDPARENT IS NOT NULL
)

GROUP BY A.PARENT
HAVING COUNT(PARENT) > 1
ORDER BY COUNT(PARENT) DESC
``````
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
TextReport, was your "as stated"  comment directed toward my suggestion?

If so, why?  I see our queries as equivalent, except you added an extra sort
We both check grandparent not null and compare parents against grandparents.  (I use not exists, you use not in)
Different syntax but same operations.

If not directed at my suggestion, then what did you mean?