Select COUNT with inner join

162
September 28, 2018, at 12:00 PM

I have two tables:

project (id, name)
list (id, name, project_id)

I wanna get every project and number of list into the project.

I've tryed:

SELECT project.id, project.name, COUNT(list.id) AS list_count
FROM project 
INNER JOIN list ON (list.project_id = project.id)
GROUP BY project.id DESC

But I get only the project that have list.

If the project don't own list, I don't get it...

I want list_count = 0 if COUNT(list_id) = 0, how must I do please ?

Answer 1

Use left join instead of Inner join

SELECT project.id, project.name, COUNT(list.id) AS list_count
FROM project 
Left JOIN list ON (list.project_id = project.id)
GROUP BY project.id DESC
Rent Charter Buses Company
READ ALSO
List movies directed by one of the movie actors [on hold]

List movies directed by one of the movie actors [on hold]

I have a database with the follow:

140
MySQL Query with insert specific data

MySQL Query with insert specific data

I would like to replace/save data from another table into my WordPress databaseI wonder if you can write this one query or do it with PHP (this way is much slower !!) I would like the query to be:

150
PHP MySQL How to query specific value from column

PHP MySQL How to query specific value from column

I have a MySQL-table wih the columns

138