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
-
05:30
-
04:00
-
00:00
-
10:30
-
9:10
-
07:40
Firebase Cloud Functions: PubSub, "res.on is not a function"
-
04:00
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
-
9:20
-
7:40
POPULAR ONLINE
- typescript: tsc is not recognized as an internal or external command, operable program or batch file 111368 visits
- search for elements in a list 111047 visits
- In Chrome 55, prevent showing Download button for HTML 5 video 98808 visits
- RxJS5 - error - TypeError: You provided an invalid object where a stream was expected 96306 visits
- Ionic 2 - how to make ion-button with icon and text on two lines? 92498 visits
- NetBeans IDE - ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver 62020 visits
- Adding methods to es6 child class 22362 visits
!["SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018' for column 'created_at' at row 1 (SQL: insert into `news`](https://cmsdk.com/images/nophoto.png "\"SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018' for column 'created_at' at row 1 (SQL: insert into `news`")