MySQL inner join three tables to use in calendar

Question

Following up my question where I used the answer to generate data on my calendar called maintenance calendar showing the aircraft's maintenance schedule. This is the MySQL query for it:

SELECT DISTINCT s.reg  AS 'reg', 
                a.date AS 'date' 
FROM   (SELECT Curdate() 
               + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) day AS Date 
        FROM   (SELECT 0 AS a 
                UNION ALL SELECT 1 
                UNION ALL SELECT 2 
                UNION ALL SELECT 3 
                UNION ALL SELECT 4 
                UNION ALL SELECT 5 
                UNION ALL SELECT 6 
                UNION ALL SELECT 7 
                UNION ALL SELECT 8 
                UNION ALL SELECT 9) AS a 
               CROSS JOIN (SELECT 0 AS a 
                           UNION ALL SELECT 1 
                           UNION ALL SELECT 2 
                           UNION ALL SELECT 3 
                           UNION ALL SELECT 4 
                           UNION ALL SELECT 5 
                           UNION ALL SELECT 6 
                           UNION ALL SELECT 7 
                           UNION ALL SELECT 8 
                           UNION ALL SELECT 9) AS b 
               CROSS JOIN (SELECT 0 AS a 
                           UNION ALL SELECT 1 
                           UNION ALL SELECT 2 
                           UNION ALL SELECT 3 
                           UNION ALL SELECT 4 
                           UNION ALL SELECT 5 
                           UNION ALL SELECT 6 
                           UNION ALL SELECT 7 
                           UNION ALL SELECT 8 
                           UNION ALL SELECT 9) AS c) a 
       INNER JOIN maintenance_sched s 
               ON a.date >= s.date_from 
                  AND a.date <= s.date_to 
WHERE  Month(date) = '".$month."' 
       AND Dayofmonth(date) = '".$dayArray["mday"]."' 
       AND Year(date) = '".$year."'

Here is the maintenance_sched database:

maintenance_sched table

And the calendar looks like this (based on the data from maintenance_sched):

enter image description here

Then, I have another calendar called reservation calendar with the same code as the maintenance calendar though with different query. This is the reservation calendar query: SELECT acode FROM reservation WHERE month(etd) = '".$month."' AND dayofmonth(etd) = '".$dayArray["mday"]."' AND year(etd) = '".$year."' ORDER BY etd".

The reservation table is this:

enter image description here

And the reservation calendar looks like this:

enter image description here

EDIT:

What I want to do is: have these two calendar in one calendar with the result of maintenance_sched query outputted as string with strikethrough. But I can't seem to make the two queries work out together as one.

Nice images but any chance you can post the relevant sql instead of all that code? — Popnoodles, Mar 12 '13 at 01:30
I notice you are executing a mysql_query inside of a for loop. Firstly, you should not use mysql_query as it is soon to be deprecated, use mysqli or pdo instead. Secondly you should typically query once, then loop through your results multiple times on the application side. What you have written seems highly inefficient at first glance. — Joe Meyer, Mar 12 '13 at 01:35
The SQL are within the code. I posted all that codes for better understanding of what I'm trying to do. — xjshiya, Mar 12 '13 at 01:36
@JoeMeyer, thanks for reminding. I'll be sure to change that after this issue has been workout. — xjshiya, Mar 12 '13 at 01:39
You might want to consider [using this](http://www.sensefulsolutions.com/2010/10/format-text-as-table.html) to create your tables rather than images. It makes it easier for others to recreate your sample data. For example if they wanted to use the text to table in sqlfiddle.com — Conrad Frix, Mar 12 '13 at 01:50
Is there any reason you don't just UNION your first calendar query with your second calendar query? — Joe Meyer, Mar 12 '13 at 02:05
@JoeMeyer, like how? Can you please guide me for the right syntax on how to do it? And if I'm not mistaken, you can't union tables with different number of columns. — xjshiya, Mar 12 '13 at 02:23
You can, you just need to null fill fields that don't exist in your shorter table. such as SELECT id, date, description FROM table1 UNION SELECT id, date, null FROM table2 — Joe Meyer, Mar 12 '13 at 03:02

score 0 · Answer 1 · answered Mar 12 '13 at 04:24

I do think the answer to this question is to simply join the two queries. An example of this might be like below where you just null out any columns that aren't in your second table.

SELECT id, date, field3, description 
FROM table1 
UNION 
SELECT id, date, field3, null 
FROM table2

score 0 · Answer 2 · answered Mar 12 '13 at 05:38

As there is no relationship among both the table we cannot go for joins, it would be better to go for UNION to combine the result.

This query uses group_concat so will generate common results in following form

2013-03-15 | RP-C1728, RP-C1086

2013-03-08 | RP-C1728, RP-C1086, RP-C143

If you dont want record in this format then just remove group_concat, group by clause from the query.

Query

SELECT a.date, group_concat(a.reg) 
FROM 
     (SELECT DISTINCT s.reg  AS 'reg', 
                a.date AS 'date' 
        FROM   (SELECT Curdate() 
               + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) day AS Date 
            FROM   (SELECT 0 AS a 
                UNION ALL SELECT 1 
                UNION ALL SELECT 2 
                UNION ALL SELECT 3 
                UNION ALL SELECT 4 
                UNION ALL SELECT 5 
                UNION ALL SELECT 6 
                UNION ALL SELECT 7 
                UNION ALL SELECT 8 
                UNION ALL SELECT 9) AS a 
               CROSS JOIN (SELECT 0 AS a 
                           UNION ALL SELECT 1 
                           UNION ALL SELECT 2 
                           UNION ALL SELECT 3 
                           UNION ALL SELECT 4 
                           UNION ALL SELECT 5 
                           UNION ALL SELECT 6 
                           UNION ALL SELECT 7 
                           UNION ALL SELECT 8 
                           UNION ALL SELECT 9) AS b 
               CROSS JOIN (SELECT 0 AS a 
                           UNION ALL SELECT 1 
                           UNION ALL SELECT 2 
                           UNION ALL SELECT 3 
                           UNION ALL SELECT 4 
                           UNION ALL SELECT 5 
                           UNION ALL SELECT 6 
                           UNION ALL SELECT 7 
                           UNION ALL SELECT 8 
                           UNION ALL SELECT 9) AS c) a 
       INNER JOIN maintenance_sched s 
               ON a.date >= s.date_from 
                  AND a.date <= s.date_to 
WHERE  Month(date) = '".$month."' 
       AND Dayofmonth(date) = '".$dayArray["mday"]."' 
       AND Year(date) = '".$year."' 
UNION ALL 
SELECT acode as 'reg', date as 'date' //Add the date logic here as per your need
FROM reservation 
WHERE month(etd) = '".$month."' AND 
dayofmonth(etd) = '".$dayArray["mday"]."' AND 
year(etd) = '".$year."' ORDER BY etd) a
GROUP BY a.date;

NOTE For the second query add the according date logic

MySQL inner join three tables to use in calendar

2 Answers2