Query in sql involving joins of two table
- by Satish
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 | coding | 4 | 2009-01-06 |
| prasoon | coding | 4 | 2009-01-10 |
+----------+-----------+---------+------------+
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 | Absent | Absent | |
| 2009-01-05 | | | Holi |
| 2009-01-06 | coding | 4 | |
| 2009-01-07 | Absent | Absent | |
| 2009-01-08 | Absent | Absent | |
| 2009-01-09 | Absent | Absent | |
| 2009-01-10 | coding | 4 | |
+-------------+-----------+---------+-------------------+
In other words I want to fill the activity and hours columns with "Absent" on the dates which are neither in table reports nor in table holidays. How can I write a specific query for it. The query should give the output between two specific dates.
