-1

I'm trying to get some data from my SQL database but I have some issue that I don't understand. This is my query :

SELECT SUM(t0_.in_budget_hours) AS sclr_0, 
SUM(t0_.billable_amount) AS sclr_1, 
SUM(t0_.out_budget_hours) AS sclr_2, 
SUM(t0_.unprod_hours) AS sclr_3, 
SUM(t0_.off_hours) AS sclr_4,
SUM(t0_.duration) AS sclr_5, 
SUM(t0_.in_budget_hours) / SUM(t0_.duration) * 100 AS sclr_6, 
CONCAT(c1_.name, ' ', c1_.lastname) AS sclr_7, 
c1_.alias AS alias_8, 
c1_.email AS email_9 
FROM collaborator c1_
RIGHT JOIN timesheet t0_ ON (c1_.email = t0_.email) 
WHERE t0_.entry_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-22 23:59:59' 
GROUP BY sclr_7, c1_.alias, c1_.email 
ORDER BY sclr_7 ASC

I have a lot of information calculated from timesheet table and I would like to merge these calcul with the collaborator name.

I would like to have All collaborators and join them with timesheets, even if they don't have any timesheet (LEFT JOIN). If there is no row related to the collaborator, the sum result should be 0 but the collaborator should appear in result dataset. But currently If the collaborator do not have any timesheet BETWEEN '2020-01-01 00:00:00' AND '2020-01-22 23:59:59', he will not appear in results.

I tryied several type of join using this thread What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? but nothing worked for me. Can someone tell me how to have all collaborator even if there is no timesheet ?

Shining
  • 425
  • 6
  • 20
  • `WHERE (a0_.entry_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-22 23:59:59') OR (a0_.entry_date IS NULL)` could work – Cid Jan 22 '20 at 13:01
  • What is your `a0_` table? it doesn't seem to be declared in your snippet – Pepper Jan 22 '20 at 13:02
  • `WHERE (a0_.entry_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-22 23:59:59') OR (a0_.entry_date IS NULL)` do not work but ty for the proposition. `a0_ `is `t0_`, sorry for the mistakes I have fixed that. – Shining Jan 22 '20 at 13:07
  • You typically `GROUP BY` the same columns as you `SELECT`, except those who are arguments to set functions. – jarlh Jan 22 '20 at 13:28
  • I don't have any issue with group by so is this related to my joins problems ? – Shining Jan 22 '20 at 13:32
  • @Shining, my bad, didn't notice sclr_7 was column alias, thought it was a column. – jarlh Jan 22 '20 at 14:09

2 Answers2

2

I would like to have All collaborators and join them with timesheets, even if they don't have any timesheet (LEFT JOIN).

Your RIGHT JOIN seems to do the opposite of this. As it is, it will take all rows in entry_date, even those without a matching record in collaborator. RIGHT JOINs are quite counter-intuitive for people that are used to reading from left to right.

I would rewrite your JOIN as the following LEFT JOIN instead:

FROM collaborator c1_
LEFT JOIN timesheet t0_ 
    ON c1_.email = t0_.email
    AND t0_.entry_date >= '2020-01-01'
    AND t0_.entry_date < '2020-01-23'

Side notes:

  • conditions on timesheet need to be moved from the WHERE clause to the ON part of the JOIN

  • I rewrote the date filter to use half-open intervals, which I find more convenient to read and understand

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks but this is not working. I tried left join before right join. Also I tried to change the source table (from collaborator <=> from timesheet) but not working. – Shining Jan 22 '20 at 13:14
  • @Shining: note that I also moved the conditions from the `WHERE` clause to the `ON ` part of the `LEFT JOIN`, did you try that already? Please give it a try against your real data. – GMB Jan 22 '20 at 13:15
  • In my initial sql request, I have the same clause `ON` as you. I did not understand well what you have changed, for me you changed only RIGHT JOIN to LEFT JOIN but nothing else. (the between clause is the same as your date filter) – Shining Jan 22 '20 at 13:20
  • 1
    @Shining: look closer. Your filter on `entry_date` is in the `WHERE` clause (`WHERE t0_.entry_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-22 23:59:59'`). I changed it so it is in the `ON` clause of the `LEFT JOIN`. So basically my query does not have a `WHERE` clause at all. – GMB Jan 22 '20 at 13:31
  • You are the best ! This is my mistake ! Thank you. Do you have some documentation that explain me my mistake more precisely ? I mean the difference between the clause ON and WHERE in a joins ? thank you again man ! – Shining Jan 22 '20 at 13:37
  • @Shining, because your "missing" collaborators only had rows with joins from timesheet entries outside of your range, and then the `where` clause removed all those rows (therefore removing the collaborator from the end result) – Pepper Jan 22 '20 at 13:43
0

You need an OUTER join to join two tables where there may not be any matching rows in one of the two tables.

By default joins are INNER, meaning only rows where there is a match are returned.

Looks to me like a LEFT OUTER should give you what you want.

select * from a LEFT OUTER JOIN b means all rows from a, and matching rows (or NULLs) from b

More details here

James Stevens
  • 374
  • 2
  • 8
  • `SELECT SUM(t0_.in_budget_hours) AS sclr_0, SUM(t0_.billable_amount) AS sclr_1, SUM(t0_.out_budget_hours) AS sclr_2, SUM(t0_.unprod_hours) AS sclr_3, SUM(t0_.off_hours) AS sclr_4, SUM(t0_.duration) AS sclr_5, SUM(t0_.in_budget_hours) / SUM(t0_.duration) * 100 AS sclr_6, CONCAT(c1_.name, ' ', c1_.lastname) AS sclr_7, c1_.alias AS alias_8, c1_.email AS email_9 FROM collaborator c1_ LEFT OUTER JOIN timesheet t0_ ON (c1_.email = t0_.email) WHERE t0_.entry_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-22 23:59:59' GROUP BY sclr_7, c1_.alias, c1_.email ORDER BY sclr_7 ASC` do not work – Shining Jan 22 '20 at 13:11
  • 1
    If i'm not mistaken, `LEFT JOIN` is the same as `LEFT OUTER JOIN` for mysql – Pepper Jan 22 '20 at 13:12
  • I was pretty certain the default `JOIN` on MySQL (& most RDBMS) is `INNER`. I couldn't easily find a reference to it in the MySQL docs, but it is [stated clearly here](https://hackernoon.com/mysql-tutorial-query-limit-offset-condition-row-foreign-key-join-select-d3835b28e1a) and [here](https://stackoverflow.com/questions/4418776/what-is-the-default-mysql-join-behaviour-inner-or-outer) – James Stevens Jan 22 '20 at 13:51