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

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?
0
tonelm54
Asked:
tonelm54
1 Solution
 
Pratima PharandeCommented:
select count(*) from Folder where folderid=parentId
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:

Select f.*
  , (select count(*) from Folder p where p.folderid = f.parentid ) parent_count 
 from Folder f

Open in new window

0
 
HainKurtSr. System AnalystCommented:
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)
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
tonelm54Author Commented:
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

0
 
sameer2010Commented:
Then try
select parentID, count(*) from Folder group by parentID ?
0
 
sameer2010Commented:
Add parentid = @parentid if you meant that
0
 
HainKurtSr. System AnalystCommented:
try:

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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