Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: List of records with count(*) in child table

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just one query
    Code:
    SELECT TBParent.id
         , COUNT(TBChild.parentID) AS children
      FROM TBParent 
    LEFT OUTER
      JOIN TBChild
        ON TBChild.parentID = TBParent.id
    GROUP
        BY TBParent.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    144
    tanX, but this query is very time-consuming, my desktop went to coma!

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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?

  5. #5
    Join Date
    May 2004
    Posts
    144
    i need how many childeren each parent have.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2004
    Posts
    144
    tHank you r937, & what about knowin which parent has no children?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2005
    Posts
    30
    If you want to get only those parents who have no children you can try this:

    Code:
    SELECT TBParent.id
         , COUNT(TBChild.parentID) AS children
      FROM TBParent 
    LEFT OUTER
      JOIN TBChild
        ON TBChild.parentID = TBParent.id
    GROUP
        BY TBParent.id
    HAVING
        children=0;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •