Mysql – How to JOIN by date columns

join;MySQL

I have two tables with the structure of

CREATE TABLE table1
(
Month date,
Value1 varchar(255),
PRIMARY KEY(Month)
) ENGINE=InnoDB

CREATE TABLE table2
(
Day date,
Value2 varchar(255),
PRIMARY KEY(Day)
) ENGINE=InnoDB

In table1, the date values are stored in the form of

and in table2

How can I JOIN two tables to have the month value (Value1 from table1) for each row of table2?

The desirable result

Day           Month        Value1        Value2
2018-04-01    2018-04-01   
2018-04-02    2018-04-01
2018-04-03    2018-04-01
2018-04-04    2018-04-01
2018-04-05    2018-04-01

Best Answer

Just use LEFT JOIN + DATE_FORMAT:

SELECT
  t2.Day,
  t1.Month,
  t1.Value1,
  t2.Value2
FROM Table2 t2 LEFT JOIN Table1 t1 ON DATE_FORMAT(t2.Day, '%Y-%m-01') = t1.Month

http://www.sqlfiddle.com/#!9/920c79/1

Related Solutions

Mysql – index all columns of a table in MySQL safely

When it comes to querying, indexing of a table should never be your first concern.

The queries you plan to use should dictate the indexes you need.

Based on the queries, some columns can be individually indexed. Other queries require compound indexes. The ORDER BY and GROUP BY clauses should provide immediate hints for indexes to make. Not using such hints may result in temp table sorting rather than using the indexes for data in the desired order needed.

Low cardinality of column values should eliminate the need for an index.

Even with these things taken into consideration, you may find that query may need some adjustment (a.k.a. refactoring) for performance gains.

When you reach the point of having the right indexes, not you have to worry about the size of those indexes. For a MyISAM table, this would mean that the .MYI file may grow significantly.

The size of the index file as well as the number of indexes should now be weighed against the performance of your queries, especially if the indexes provide the proper ordering of data and fastest retrieval.

Explain plans for queries may change over time depending on the number of rows, cardinality of columns, number of DELETEs and UPDATEs. Once a query's explain plan changes from what it looked like months ago, you should explore the need to add or remove indexes.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

Mysql – index all columns of a table in MySQL safely

Mysql – Finding rows for a specified date range

Related Question