MYSQL Query multiple tables using join

Question

I am querying 3 tables and can extract the correct data using single queries to each table, when I put them in a join statement, there results that come back are different.

Queries:

select advisor, round(sum(shift),1) AS avail_time from shift_track
where date >= 20180319
group by advisor order by advisor ASC

select advisor, round(sum(time/60),1) AS standard_time from shift_report
where date >= 20180319
group by advisor order by advisor ASC

select advisor, round(sum(time/60),1) AS mobile_time from sr_mobile
where date >= 20180319
group by advisor order by advisor ASC

JOIN I am trying:

SELECT shift_track.advisor, shift_track.date, sum(shift_track.shift) AS 
avail_time, 
sum(shift_report.time/60) AS stand_time,
sum(sr_mobile.time/60) AS mobile_time
from shift_track
inner join shift_report on shift_report.advisor=shift_track.advisor
inner join sr_mobile on sr_mobile.advisor=shift_track.advisor
where shift_track.date =20180319
group BY shift_track.advisor ORDER BY shift_track.advisor ASC

I'm trying to grab the total times from each table for an advisor on a given date. Join query seems to follow the others on stack.

An alternative could be to use seperate queries in PHP and then add the results to a table to display - but i'm sure I should be able to do it in a single query??

Any help much appreciated.

Answer 1

Use union all:

select advisor, sum(avail_time) as avail_time, 
       sum(standard_time) as standard_time, 
       sum(mobile_time) as mobile_time
from ((select advisor, sum(shift) AS avail_time, NULL as standard_time, NULL as mobile_time
       from shift_track
       where date >= 20180319
       group by advisor
      ) union all
      (select advisor, NULL, sum(time/60) AS standard_time, NULL
       from shift_report
       where date >= 20180319
       group by advisor
      ) union all
      (select advisor, NULL, NULL, sum(time/60) AS mobile_time
       from sr_mobile
       where date >= 20180319
       group by advisor
      )
     ) t
group by advisor
order by advisor;

If you know that all advisors are in all three tables, then you can use a join. However, this guarantees that all advisors are in the results.