myTable
---------------------
id pid name
1 0 title1
2 1 title1
3 1 title3
4 2 title4
5 0 title5
6 0 title6
---------------------

when I use myfile.php?pid=0
The Result I expected:
---------------------
id pid name counter
1 0 title1 2
5 0 title5 0
6 0 title6 0
---------------------

my SQL:
---------------------
SELECT tbl1.*,COUNT(tbl2.id) as counter
FROM myTable AS tbl1, myTable AS tbl2
WHERE tbl1.id=tbl2.pid AND tbl1.pid='{$_GET['pid']}'
GROUP BY tbl1.id

my Result:
---------------------
id pid name counter
1 0 title1 2

Lost:
--------------------
5 0 title5 0
6 0 title6 0

How Can I write the SQL? Please help me.

Hi there,

I'm not sure what you're trying to do, so excuse me if I'm wrong, but you have:


id pid name
1 0 title1
2 1 title1
3 1 title3
4 2 title4
5 0 title5
6 0 title6


And for the sake of simplicity, take out all records whose pid is not equal to 0, as you intended:



id pid name
1 0 title1
5 0 title5
6 0 title6


Then, there are only 3 records left. If we add up the conuter value of the list you provided that you wish to retrieve, it becomes 4... Exactly what are you counting?

If you're trying to count the number of 'name' that has 'pid' of 0, you should have the second record's pid set to 0, and not 1.

Anyway, assmuming that my assumption is correct, you can run the following query to get what you want:


SELECT *, COUNT(name) AS 'counter'
FROM myTable
WHERE pid = 0
GROUP BY name;


Since you are counting the occurence of the 'name' values of pid = 0, you should count 'name' fields. HTH.

thank you very much.
I think I will explain the dest of mine.
------------------------------------
CATEGORY1
category1_1
category1_2
CATEGORY2
CATEGORY3
CATEGORY4
category4_1
category4_1_1
CATEGORY5
------------------------------------
when I use myfile.php?pid=0, i expect to get the reuslt just like this:
------------------------------------
Category 1 (2 subcategories)
Category 2 (0 subcategory)
Category 3 (0 subcategory)
Category 4 (1 subcategory)
Category 5 (0 subcategory)

Now I see what you are driving at.

You can use OUTERJ JOIN to get null values (i.e. counter = 0):


SELECT t1.*, COUNT(t2.pid) AS 'counter'
FROM myTable t1 LEFT JOIN myTable t2 ON t1.id = t2.pid
WHERE t1.pid = 0
GROUP BY t1.id;


This, however, will only get the children, and not ancestors, as you indicated in the last post's example.

I hope this is what you wanted. Good night.

a small remark poped-up here :p
what is the PHP code you are using?
somehow I feel like you aren't using a loop to get all your records from MySQL.. !!
because the first SQL query in your first post seems ok for me!

maybe I am wrong but I thought this may be the problem :) !

Yes, you are right :)
I am programing a script using MySQL and ColdFusion.

thank you again!!!