It is necessary to select all the rows from the table and add the resulting field to the row with counting the number of records from another table by keys. I do this:

SELECT `kindergarten`.`Name`, `kindergarten`.`Director`, `kindergarten`.`Address`, `kindergarten`.`Phone`, `kindergarten`.`IdKindergarten`, `kindergarten`.`Web`, `MaxKids`, COUNT(kind.IdKind) AS ActiveKids FROM `kindergarten` LEFT JOIN `kind` ON `kind`.`IdKindergarten` = `kindergarten`.`IdKindergarten` ORDER BY `IdKindergarten` DESC LIMIT 20

Gives one record

1 answer 1

You can do the following:

 SELECT `kindergarten`.`Name`, `kindergarten`.`Director`, `kindergarten`.`Address`, `kindergarten`.`Phone`, `kindergarten`.`IdKindergarten`, `kindergarten`.`Web`, `MaxKids`, COUNT(kind.IdKind) AS ActiveKids FROM `kindergarten` LEFT JOIN `kind` ON `kind`.`IdKindergarten` = `kindergarten`.`IdKindergarten` ORDER BY `IdKindergarten` DESC GROUP BY ActiveKids LIMIT 20