LEFT OUTER JOIN not showing all rows?

Question

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?

peterm · Accepted Answer · 2013-07-17T08:02:27.190

4

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

edited Jul 17 '13 at 08:02

answered Jul 17 '13 at 07:14

peterm

91,357
15
148
157

This worked! Thanks! Could you please explain me why? I now know that it's works, but I don't really have an idea why it works. – Jul 17 '13 at 07:16
1

You should use `GROUP BY` since you're applying an aggregate function `COUNT()` and want to get results per group (in your case per project) and not a single value. – peterm Jul 17 '13 at 07:18
I'm actually quite interested now, how does the performance of this look compared to a correlated subquery? Would it be similar or could this be potentially more efficient? – Kaiwa Jul 17 '13 at 07:20
Thanks peterm, you really helped me here – Jul 17 '13 at 07:21
In MySql [Subqueries perform poorly](http://mysql.rjweb.org/doc.php/ricksrots). That being said as always with performance you have look at execution plan with `EXPLAIN` and benchmark queries. – peterm Jul 17 '13 at 07:26
Wait... project 2 has 1 participant in the result (in your [SQLFiddle](http://sqlfiddle.com/#!2/8da5f/3)) while there aren't any in the source participants table. I think you missed a check on NULL on the participants count. (Kaiwa's solution gives the correct result) – Rik Jul 17 '13 at 07:58
@Rik Good catch. Thanks. It's fixed in the answer and sqlfiddle. – peterm Jul 17 '13 at 08:03

score 3 · Answer 2 · answered Jul 17 '13 at 07:11

3

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

answered Jul 17 '13 at 07:11

Kaiwa

151
7

But according to this: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html It should be possible to do it with a join, some records should contain null or something like that. I know it can be done with a subquery, but it doesn't look as nice as a join I think. – Jul 17 '13 at 07:12
Then you will have to look at peterm his solution. Both these queries give the same result, his is probably the one you want! – Kaiwa Jul 17 '13 at 07:16

LEFT OUTER JOIN not showing all rows?

2 Answers2