Unanswered: List of records with count(*) in child table
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.