Join count MySQL

More tags

Join count MySQL
How to join results from one MySQL table while counting matching records in another table and sorting the result according to the count of the 2nd table using PHP.
Author: SimplytheBest Inc Price: Free Type: PHP,MySQL

How to join results from one MySQL table while counting matching records in another table and sorting the result according to the count of the 2nd table using PHP.

THE CASE

Table-1:

This table contains the records of f.e. authors, customers or something similar.

Table-2:

This table contains the records of the contributions, files, or similar of Table-1 persons.

Wanted results:

We want results to show persons of Table-1 sorted by the number of items in Table-2. The more contributions, the higher the ranking.

The query:

Field can be f.e. the record ID of Table-1 which is f.e. author_ID in Table-2. The GROUP BY field would then be Table-1.id.

 

$sql = "SELECT *, COUNT(*) as totalcount FROM Table-2 LEFT JOIN Table-1 ON Table-2.field = Table-1.field GROUP BY Table-1.field ORDER BY totalcount DESC";

That's all there is to it.

Hope this makes you happy enough to link to us! =:)