MySQL where date on right join

Question

I have this query. I want to display the count of each shifts then I wrote the query like this:

SELECT count(checkups_id) as checkup_count, shifts.description, shifts.shifts_id
from checkups
right join shifts on shifts.shifts_id=checkups.shifts_id
group by shifts.shifts_id

Result:

checkup_count	description	shifts_id
4	Morning Shift	1
7	Afternoon Shift	2

But then I want to display the current date checkup_count using where date like this:

SELECT count(checkups_id) as checkup_count, shifts.description, shifts.shifts_id
from checkups
right join shifts on shifts.shifts_id=checkups.shifts_id
where date(checkups.created_at) = '2021-09-13'
group by shifts.shifts_id

Result:

checkup_count	description	shifts_id
2	Morning Shift	1

I need to get the Afternoon Shift checkup_count (which is currently 0), so what I want to do is to display the result like this:

checkup_count	description	shifts_id
2	Morning Shift	1
0	Afternoon Shift	2

How do I write the query?

You can use `shifts` as main table and left join `checkups` table. — newman, Sep 13 '21 at 07:19
If you use RIGHT JOIN and want to apply some conditions by left table then you must put these conditions into ON clause, not into WHERE. PS. Convert RIGHT JOIN to LEFT. — Akina, Sep 13 '21 at 07:47

score 1 · Accepted Answer · answered Sep 13 '21 at 11:56

I would recommend using left join instead of right join. It is simply easier to follow ("keep all rows in the first table" rather than "keep all rows in some table whose name I haven't read yet").

Then, you can to filter in the on clause. Otherwise, you turn the outer join into an inner join when you start filtering.

So:

select count(c.heckups_id) as checkup_count, s.description, s.shifts_id
from shifts s left join
     checkups c
     on c.shifts_id = s.shifts_id and
        date(c.created_at) = '2021-09-13'
group by s.shifts_id;

Note that I introduced table aliases so the query is easier to write and read. I would also suggest writing the date comparison as:

     on c.shifts_id = s.shifts_id and
        c.created_at >= '2021-09-13' and
        c.created_at < '2021-09-14'

Although this looks more complicated, it is more optimizer-friendly because it avoids a function call on a column.

Thank you so much for your help. I really appreciate it – DenjanD Sep 14 '21 at 12:52 — DenjanD, Sep 14 '21 at 12:52

Ali Fidanli · Answer 2 · 2021-09-13T08:36:34.737

You can add 2 rows with union all each shift id , so you guarantee that sum of these unions return rows:

I added these statements to your original query :

union all
select 0 ,  'Morning Shift' ,   1
union all  
select 0 ,  'Afternoon Shift' , 2

and i put them all into subquery afterwards summed the numbers:

Select sum(g.checkups_id), g.description , g.shifts_id from 
(
SELECT count(checkups_id) as checkup_count, shifts.description, shifts.shifts_id
from checkups
right join shifts on shifts.shifts_id=checkups.shifts_id
where date(checkups.created_at) = '2021-09-13'
group by shifts.shifts_id
union all
select 0 ,  'Morning Shift' ,   1
union all  
select 0 ,  'Afternoon Shift' , 2 
) g 
group by g.description , g.shifts_id

Update: You can Use Left Join to avoid these extra unions. Like This

Select s.description, s.shifts_id , count(*) checkup_count
from shifts s left join checkups c on  s.shifts_id=c.shifts_id
where date(c.created_at) = '2021-09-13'
group by s.description, s.shifts_id

MySQL where date on right join

2 Answers2