asked on

Count of ParentFolders

Im trying to create a table of folders which I would like to be able to see if there is any children folders below.

I have a table with 3 coloums:-

folderID, ParentID, Folder

The ParentID has the value of folderID of its parent.

And I query it simply like

Select * from Folder

However I want it to be able to count the number of folders that have the parentID the same as the folderID, so something like:-

Select *, (Countif(`parentID`=`folderID`)) from Folders;

I know I need to make a subquery, but Im unsure how to do this, does anyone have any advice?

Pratima

select count(*) from Folder where folderid=parentId

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

I gueess you have @folderid, and you want the count of records/folders in same parent

select count(*) from Folder where parentId = (select parentid from folder where folderid=@folderid)

tonelm54

ASKER

Ok, so Ive put in:-

SELECT * , (SELECT count(*) FROM Folder WHERE `ParentID` =`folderID`) As subFolders
FROM`Folder`

Open in new window

However it is always returning 0

Just as an example, One of my rows has

folderID = 1842-40090-42653
parentID = 83770-6566-264

If I do

SELECT* FROM`Folder` WHERE`ParentID` LIKE '83770-6566-264';

I get 5 rows returned

sameer2010

Then try
select parentID, count(*) from Folder group by parentID ?

sameer2010

Add parentid = @parentid if you meant that

HainKurt

try:

SELECT a.* , (SELECT count(*) FROM Folder b WHERE b.ParentID =a.folderID) As subFolders
FROM Folder a