Specific query in Mysql
- by Radhe
I have two tables reports and holidays.
reports: (username varchar(30),activity varchar(30),hours int(3),report_date date)
holidays: (holiday_name varchar(30), holiday_date date)
select * from reports gives
+----------+-----------+---------+------------+
| username | activity | hours | date |
+----------+-----------+---------+------------+
| prasoon | testing | 3 | 2009-01-01 |
| prasoon | coding | 4 | 2009-01-03 |
| prasoon | designing| 2 | 2009-01-04 |
| prasoon | coding | 4 | 2009-01-06 |
+----------+-----------+---------+------------+
select * from holidays gives
+--------------+---------------+
| holiday_name | holiday_date |
+--------------+---------------+
| Diwali | 2009-01-02 |
| Holi | 2009-01-05 |
+--------------+---------------+
Is there any way by which I can output the following?
+-------------+-----------+---------+-------------------+
| date | activity | hours | holiday_name |
+-------------+-----------+---------+-------------------+
| 2009-01-01 | testing | 3 | |
| 2009-01-02 | | | Diwali |
| 2009-01-03 | coding | 4 | |
| 2009-01-04 | designing| 2 | |
| 2009-01-05 | | | Holi |
| 2009-01-06 | coding | 4 | |
+-------------+-----------+---------+-------------------+