MySQL: limit results to items with at least one child item

With MySQL, I want to limit results to items with at least one child item.

My table has a column named `ParentID` and I only want results where at least one item uses the item returned as its `ParentID`

SELECT `Name`,`ID` FROM `File` WHERE `ClassName` = 'Folder' AND  (there is at least one `File` with the `ParentID` of this `ID`)

Open in new window

LVL 16
hankknightAsked:
Who is Participating?
 
Walter RitzelConnect With a Mentor Senior Software EngineerCommented:
SELECT `Name`,`ID` FROM `File` F1 WHERE `ClassName` = 'Folder' AND  exists (select 1 from `File` where `parentid` = F1.`ID` group by `parentid` having count(1) >=1)
0
 
hieloConnect With a Mentor Commented:
try:
SELECT `Name`,`ID` FROM `File` WHERE `ClassName` = 'Folder' 
AND  `File`.`ID` IN (SELECT `f2`.`ID` FROM `File` as `f2` WHERE `f2`.`ID`=`File`.`ID`)

Open in new window

0
 
hankknightAuthor Commented:
Thank you both.  hielo, I modified your code to make it work.  

wpcortes, your code works as is.

SELECT `Name`,`ID` FROM `File` WHERE `File`.`ClassName` = 'Folder' 
AND `File`.`ID` IN (SELECT `f2`.`ParentID` FROM `File` as `f2` WHERE `f2`.`ParentID`=`File`.`ID`)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.