CODEWITHSUNDEEP
mysqlsqlleft-joinouter-join
user936965
user936965

Reputation:

LEFT OUTER JOIN not showing all rows?

I'm trying to get data from 2 tables with one SQL statement using joins. The idea is quite simple. A project has participants, and in a project overview I want to show the project info with the amount of participants.

Right now there are 2 projects, one project with participants and the other project without participants.

I use this query:

SELECT SQL_CALC_FOUND_ROWS `p`.`id`,
       `p`.`title`, 
       `p`.`live`, 
       `p`.`startDate`,
       `p`.`endDate`, 
       COUNT(`part`.`id`) AS `participants`
FROM `projects` `p`
LEFT OUTER JOIN `participants` `part`
    ON `p`.`id`  = `part`.`projectid`
ORDER BY `p`.`live` DESC, 
         `p`.`startDate` DESC
LIMIT 0,10

Problem is, this query only returns the project with participants and the one without participants is left out.

What am I doing wrong here?

Upvotes: 0

Views: 405

Answers (2)

peterm
peterm

Reputation: 92785

You have to use GROUP BY

SELECT SQL_CALC_FOUND_ROWS `p`.`id`,
       `p`.`title`, 
       `p`.`live`, 
       `p`.`startDate`,
       `p`.`endDate`, 
       COUNT(`part`.`id`) AS `participants`
FROM `projects` `p`
LEFT OUTER JOIN `participants` `part`
    ON `p`.`id`  = `part`.`projectid`
GROUP BY `p`.`id`,
         `p`.`title`, 
         `p`.`live`, 
         `p`.`startDate`,
         `p`.`endDate`
ORDER BY `p`.`live` DESC, 
         `p`.`startDate` DESC
LIMIT 0,10

Here is SQLFiddle demo

Upvotes: 4

Kaiwa
Kaiwa

Reputation: 151

I don't think this should be done with a JOIN but rather with a correlated subquery.

SELECT SQL_CALC_FOUND_ROWS `p`.`id`,
   `p`.`title`, 
   `p`.`live`, 
   `p`.`startDate`,
   `p`.`endDate`, 
   (SELECT COUNT(`part`.`id`) FROM `participants` `part` WHERE `part`.`projectid` = `p`.`id`) AS `participants`
FROM `projects` `p`
ORDER BY `p`.`live` DESC, 
     `p`.`startDate` DESC
LIMIT 0,10

Upvotes: 3

Related Questions