• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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

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

Open in new window

0
endrec
Asked:
endrec
  • 3
  • 3
1 Solution
 
TextReportCommented:
Your test data doesn't inclyude any records that match your criteria

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

Cheers, Andrew
0
 
sdstuberCommented:
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
0
 
TextReportCommented:
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

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sdstuberCommented:
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?
0
 
sdstuberCommented:
I'm not offended, just curious.
thanks
0
 
TextReportCommented:
No not at all, just wanted to point out that endrec was almost there.
Cheers, Andrew
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now