MySQL query for "departments LEFT JOIN products" grouped by department_id does not return data for all departments

Question

I have two tables - departments and products. I am trying to join the two tables using the following query:

SELECT departments.department_id, departments.department_name, departments.over_head_costs, SUM(products.product_sales) as product_sales, products.product_sales - departments.over_head_costs AS profit
FROM departments
LEFT JOIN products
ON departments.department_id = products.department_id
GROUP BY products.department_id

The issue I'm having is that this query only returns data for some of the departments. I would like to see the data for all departments, even if there is no product that has that department_id. Is there any way to do this? I've tried many different joins, but can't get the result I want.

Please see the images below showing the current output I'm getting:

Check this question: https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — Dez, Dec 22 '18 at 21:05
Thanks @Dez, I tried the OUTER JOIN method in the link, but it's still giving me the same result as the left join. — grammerPro, Dec 22 '18 at 21:11
This question has nothing to do with full outer joins, and there's absolutely no reason to use one to solve this problem. I have no idea how it managed to get closed as a duplicate of a question about full joins. Voting to reopen — Ilmari Karonen, Dec 25 '18 at 00:26
You don't clearly say what you want. Please read & act on [mcve]. PS Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Make your post self-contained. — philipxy, Dec 25 '18 at 00:42
Possible duplicate of [Error related to only_full_group_by when executing a query in MySql](https://stackoverflow.com/q/34115174/3404097) — philipxy, Dec 25 '18 at 00:46
@philipxy: While that question is certainly more closely related than the current dupe target, I don't think it answers the OP's question in a particularly useful way either. In particular, it doesn't address the OP's apparent confusion about how left joins work at all. If the OP was already familiar enough with the way left joins and grouping work to read between the lines and tease out the solution to their problem from the answers to that question, they wouldn't be having this problem in the first place. — Ilmari Karonen, Dec 25 '18 at 02:10

score 1 · Answer 1 · answered Dec 22 '18 at 21:19

1

Try to change your grouping clause to:

  GROUP BY departments.department_id

answered Dec 22 '18 at 21:19

Testobile Testossimo

198
1
11

Tried that, didn't help : ( – grammerPro Dec 22 '18 at 21:31
@grammerPro: Whatever else happens, making this change should definitely give you a result row for each department. If it doesn't, then the query you're actually running isn't the same one as you've shown us. – Ilmari Karonen Dec 22 '18 at 21:38
@grammerPro: did you try to reindex tables? doesn't it help? – Testobile Testossimo Dec 22 '18 at 21:52

score 1 · Accepted Answer · answered Dec 22 '18 at 21:31

I'm pretty sure your problem is that you're grouping by products.department_id instead of departments.department_id. If a department has no products, then the left join will produce (before grouping) a single result row for that department with all the products.* columns set to NULL. If you group by one of these columns — even the department ID — then the grouped result will merge all departments with no products together, since products.department_id will be NULL for all of them.

Here's a simple example to demonstrate this:

CREATE TEMPORARY TABLE foo (
  foo_id INTEGER PRIMARY KEY
);
CREATE TEMPORARY TABLE bar (
  bar_id INTEGER PRIMARY KEY,
  foo_id INTEGER NULL
);
INSERT INTO foo (foo_id) VALUES (10), (20), (30), (40), (50);
INSERT INTO bar (bar_id, foo_id) VALUES
  (1, 10), (2, 10), (3, 10),
  (4, 20), (5, 20), (6, 30);

SELECT foo.foo_id, COUNT(bar.bar_id)
  FROM foo
  LEFT JOIN bar USING(foo_id)
  GROUP BY bar.foo_id

If you run this query, your results should look something like this:

foo.foo_id  COUNT(bar.bar_id)
40          0
10          3
20          2
30          1

Uh, wait... what happened to foo_id 50? Well, what happened is that, before the grouping, the left join produced a result set like this:

foo.foo_id  bar.foo_id  bar.bar_id
10          10          1
10          10          2
10          10          3
20          20          4
20          20          5
30          30          6
40          NULL        NULL
50          NULL        NULL

Notice how there are two rows where bar.foo_id is NULL. If you then group the results on that column, those rows will get grouped together, even though they do have a different value for foo.foo_id.

While a stricter RDBMS might then complain that you're selecting the non-grouped column foo.foo_id without wrapping it in an aggregate function like COUNT() or SUM(), MySQL isn't that strict by default and will just pick an arbitrary value for that column from each group. In the example above, I got 40, but it could just as well have been 50.

Anyway, the fix is simple: just change the grouping column to departments.department_id and you'll get the results you expect. Or, to keep using my example schema, I can change the grouping column in the query above from bar.foo_id to foo.foo_id and get the following results:

foo.foo_id  COUNT(bar.bar_id)
10          3
20          2
30          1
40          0
50          0

MySQL query for "departments LEFT JOIN products" grouped by department_id does not return data for all departments

2 Answers2