Complex query in mysql

Question

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 |
| gautam   |   coding  |    1    | 2009-01-05 |  
| prasoon  |   coding  |    4    | 2009-01-06 |
| prasoon  |   coding  |    4    | 2009-01-10 |
| gautam   |   coding  |    4    | 2009-01-10 |
+----------+-----------+---------+------------+

select * from holidays gives

+--------------+---------------+  
| holiday_name |  holiday_date |
+--------------+---------------+ 
| Diwali       |   2009-01-02  |
| Holi         |   2009-01-05  |  
+--------------+---------------+

When I used the following query

 SELECT dates.date AS date,
  CASE 
    WHEN holiday_name IS NULL THEN COALESCE(reports.activity, 'Absent') 
    WHEN holiday_name IS NOT NULL and reports.activity IS NOT NULL THEN  reports.activity
  ELSE ''
    END 
  AS activity,
  CASE WHEN holiday_name IS NULL THEN COALESCE(reports.hours, 'Absent')
    WHEN holiday_name IS NOT NULL and reports.hours IS NOT NULL THEN reports.hours
    ELSE ''
    END 
  AS hours,
  CASE 
    WHEN holiday_name IS NULL THEN COALESCE(holidays.holiday_name, '')
    ELSE holidays.holiday_name
    END 
  AS holiday_name
  FROM dates 
  LEFT OUTER JOIN reports ON dates.date = reports.date 
  LEFT OUTER JOIN holidays ON dates.date = holidays.holiday_date
  where reports.username='gautam' and dates.date>='2009-01-01' and dates.date<='2009-01-10';

I got the following output

   +----------+-----------+---------+------------+  
   |  date    |  activity |  hours  |   holiday  |
   +----------+-----------+---------+------------+  
   |2009-01-05|   coding  |    1    |   Holi     |
   +----------+-----------+---------+------------+
   |2009-01-10|   coding  |    4    |            |
   +----------+-----------+---------+------------+

but I expected this

   +----------+-----------+---------+------------+  
   |  date    |  activity |  hours  |   holiday  |
   +----------+-----------+---------+------------+  
   |2009-01-01|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-02|           |         | Diwali     |
   +----------+-----------+---------+------------+
   |2009-01-03|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-04|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-05|  Coding   |   1     | Holi       |
   +----------+-----------+---------+------------+
   |2009-01-06|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-07|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-08|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-09|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-10|  Coding   |   4     |            |
   +----------+-----------+---------+------------+

How can I modify the above query to get the desired output(for a particular user (gautam in this case))?

EDIT

I also have a table dates(date date) which contains all the dates between 2009-01-01 to 2020-12-31

Answer 1

Move your WHERE clauses inside the JOIN clauses. The only thing I've changed is the last couple of lines:

FROM dates
LEFT JOIN reports ON dates.date = reports.date
AND reports.username='gautam'
LEFT JOIN holidays ON dates.date = holidays.holiday_date
AND dates.date >= '2009-01-01'
AND dates.date <= '2009-01-10'

You accepted the answer above, but further testing showed that although it works for you, it didn't work for me. Here is something that works for me:

FROM dates
LEFT JOIN reports ON dates.date = reports.date
AND reports.username='gautam'
LEFT JOIN holidays ON dates.date = holidays.holiday_date
WHERE dates.date >= '2009-01-01'
AND dates.date <= '2009-01-10'

Answer 2

Mark's answer will work great, but you are doing something that's very poor practice - you're using SQL to format your information. This is bad! You should be using it only to retrieve your information, and then format it with HTML or whatever you're pulling the data into. Your select should be a simple:

SELECT * FROM reports WHERE username='guatam'
 AND date>='2009-01-01' AND date<='2009-01-9' 

And a separate one for the holidays, if you need it:

SELECT * from holidays

And then use that information as you need.