Complex query in mysql
- 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 |
| 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))?
