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

endrecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.