hi
suppose i have 2 table (TBParent & TBChild) & every record in TBChild must have a relative record in TBParent. (a one to many relation between them).
in my application i want to show TBParent records list with count of TBChild records for every row. something like this:

TBParent Records List:

TBParentRow1: have 125 records in TBChild
TBParentRow2: have 1036 records in TBChild
TBParentRow3: have 0 records in TBChild

for this i have so many solutions,
first & most simple way is to SELECT TBParent Rows first, then execute a 'SELECT Count(*)' for every single row.
second one is create a view that count all TBChild records GROUP BY TBParent.id & then create another view from TBParent that have LEFT Join with first query.
now SELECT from second view has all columns plus count recs. but i think execute second query is very time-consuming for mysql.
but i don know what is most optimized solution.

just one query

tanX, but this query is very time-consuming, my desktop went to coma!

What are the indexes on the two tables?
You could use "show create table TBParent" etc to get this info.

Do you need to know how many children each parent has or do you just want to know which parent has no children?

i need how many childeren each parent have.

then use the query in post #2

and make sure that you have the proper indexes to avoid another coma

tHank you r937, & what about knowin which parent has no children?

well, those would be represented in the result set by a value of 0 in the children column

why do you ask? didn't you get any 0s when you tested it?

If you want to get only those parents who have no children you can try this: