Query in sql involving joins of two table

EDIT 2

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-04 |  
| 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  |  
+--------------+---------------+

EDIT

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-09';

I got the following output

   +----------+-----------+---------+------------+  
   |  date    |  activity |  hours  |   holiday  |
   +----------+-----------+---------+------------+  
   |2009-01-04|   coding  |    1    |            |
   +----------+-----------+---------+------------+

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|  Coding   |   1     |            |
   +----------+-----------+---------+------------+
   |2009-01-05|           |         | Holi       |
   +----------+-----------+---------+------------+
   |2009-01-06|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-07|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-08|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-09|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+

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

---

Update 2: Here's the answer to your new question. Note that you were using the CASE statements incorrectly - COALESCE(reports.activity, 'Absent') will return reports.activity if it is not null, and 'Absent' if it is.

First you need a table dates with the dates you want to check, like so:

CREATE TABLE dates (date date);

and then fill it manually for now:

date      
----------
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
2009-01-08
2009-01-09
2009-01-10

This can be created procedurally, but that's another topic entirely.

Here's the query, using left outer joins and a nested select:

SELECT dates.date AS date,
CASE WHEN holiday_name IS NULL THEN COALESCE(user_reports.activity, 'Absent')
 ELSE '' END AS activity,
CASE WHEN holiday_name IS NULL THEN COALESCE(user_reports.hours, 'Absent')
 ELSE '' END AS hours,
COALESCE(holidays.holiday_name, '') AS holiday_name
FROM dates
LEFT OUTER JOIN 
 (SELECT * FROM reports WHERE reports.username='guatam') AS user_reports
 ON dates.date = user_reports.report_date 
LEFT OUTER JOIN holidays ON dates.date = holidays.holiday_date
WHERE dates.date>='2009-01-01' and dates.date<='2009-01-09';

Which returns:

date        activity    hours       holiday_name
----------  ----------  ----------  ------------
2009-01-01  Absent      Absent                  
2009-01-02                          Diwali      
2009-01-03  Absent      Absent                  
2009-01-04  coding      1                       
2009-01-05                          Holi        
2009-01-06  Absent      Absent                  
2009-01-07  Absent      Absent                  
2009-01-08  Absent      Absent                  
2009-01-09  Absent      Absent       

But the real problem is that 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.

---

I'm not sure what you mean by:

fill in the missing dates...with "On leave"

You can accomplish the remainder of your request by performing a pair of outer joins to a table populated with a list of dates in the desired date range.

---

This is an extension of colinmarc's answer above, just to show how you can avoid having "Absent" in there when it's a holiday. Otherwise the answer is much the same as his.

SELECT
    d.adate AS `date`,
    CASE WHEN holiday_name IS NULL THEN coalesce(activity, 'Absent') 
    ELSE '' END AS activity,
    CASE WHEN holiday_name IS NULL THEN coalesce(hours, 'Absent') 
    ELSE '' END AS activity,
    coalesce(holiday_name, '')
FROM (
    SELECT holiday_date AS adate FROM holidays
    UNION
    SELECT report_date AS adate FROM reports
) d
LEFT JOIN reports r ON (d.adate = r.report_date)
LEFT JOIN holidays h ON (d.adate = h.holiday_date)
ORDER BY adate ASC