I would like to produce report for airlines using
My date table include series of date
Entryid | Date
----------------
1 |2016-06-01
2 |2016-06-02
4 |2016-06-03
5 |2016-06-04
6 |2016-06-05
7 |2016-06-06
My airline table include
id|name
---------
1 |Air1
2 |Air2
3 |Air3
and Route table:
id|date |airline|routename
1|2016-06-01|1 |city1-city2
2|2016-06-01|1 |city1-city3
3|2016-06-01|2 |city1-city3
4|2016-06-02|2 |city1-city3
5|2016-06-02|2 |city1-city3
6|2016-06-04|2 |city1-city3
7|2016-06-04|2 |city1-city3
8|2016-06-04|1 |city1-city3
by using these three table I want to produce result as follow"
Date |Airline|totleroute
2016-06-01 | Air1 | 2
2016-06-01 | Air2 | 1
2016-06-02 | Air1 | 0
2016-06-02 | Air2 | 2
2016-06-03 | Air1 | 0
2016-06-03 | Air2 | 0
2016-06-04 | Air1 | 1
2016-06-04 | Air2 | 2
How to produce desired result from joining three tables?
You can use a query like the following:
SELECT d.`date`, t.`name`, COUNT(r.`routename`) AS TotalRoutes
FROM `Date` AS d
CROSS JOIN (
SELECT DISTINCT r.`airline`, a.`name`
FROM Route AS r
INNER JOIN Airline AS a ON r.airline = a.id
) AS t
LEFT JOIN Route AS r ON d.`date` = r.`date` AND t.`airline` = r.`airline`
GROUP BY d.`date`, t.`airline`
The CROSS JOIN is used in order to produce a result for each date - airline combination, as suggested by the sample output in the OP.
Please try this query:
select e.date,a.name airline, count(r.id) total_route from route_tbl r
LEFT join entry_tbl e on r.date =e.date
LEFT join airline_tbl a on a.id= r.id
order by r.date
group by r.date
It seems you need a so called FULL JOIN to have all combinations of Date and Route entries with a column containing the number of routes per day and airline.
Regrettably MySQL lacks support for FULL JOIN, there is a feature request open since some years.
It is still possible to emulate such a feature. A good reading would be https://explainextended.com/2009/04/06/emulating-full-outer-join-in-mysql/