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.
- Exclude the row with the number 1 because it has children.
- Only return 3 because it occurs twice with no parent number.
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
FROM TABLETEST B
WHERE B.GRANDPARENT IS NOT NULL
GROUP BY A.PARENT
HAVING COUNT(PARENT) > 1
ORDER BY COUNT(PARENT) DESC