Query in sql involving joins of two table

Question

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))?

You keep changing your question! Open a new question, please. To do something like this, you'll need nested selects (and put the where clause with the user into the nested select). You also don't need a CASE bit for the 'holiday' column. — colinmarc, Jun 06 '10 at 19:29
I've also changed my answer below to answer your new question. — colinmarc, Jun 06 '10 at 20:10

colinmarc · Accepted Answer · 2010-06-06T20:10:02.730

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 new to MySQL and see that `COALESCE()` works like `ISNULL()`in MSSQL, am I right? TIO — abatishchev, Jun 05 '10 at 07:12
Not sure about ISNULL. And sorry, forgot to add that you need a table dates to represent the date range. Will add to the answer above — colinmarc, Jun 05 '10 at 07:14
similar, but more succinct and more useful. You can have any number of values in a coalesce() check, not just two. — El Yobo, Jun 05 '10 at 07:23
See http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates for some hints on how you can make the list of dates that you need as well. Use the function described there to create a temporary table, then join that as colinmarc describes above. — El Yobo, Jun 05 '10 at 07:25
So maybe it's better to use `IFNULL()` instead of `COALESCE()` ? — abatishchev, Jun 05 '10 at 09:10
I'm pretty sure `IFNULL()` just returns a 0 or 1, which we would have to then test. in this case `COALESCE()` works perfectly — colinmarc, Jun 05 '10 at 14:34

score 0 · Answer 2 · answered Jun 05 '10 at 05:52

0

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.

answered Jun 05 '10 at 05:52

VeeArr

6,039
3
24
45

"Missing dates" implies the dates which are neither present in `reports` table nor in `holidays` table... – Satish Jun 05 '10 at 05:54
Your answer should have been a comment. :) – Satish Jun 05 '10 at 05:55
If you perform the outer join to the holidays table first, you can then use a CASE statement to determine whether the `activity` and `hours` columns are filled from the `reports` table or the string "Absent" – VeeArr Jun 05 '10 at 06:24
I tried to do what you said but didn't succeed. Can you please elaborate. – Satish Jun 05 '10 at 06:33

El Yobo · Answer 3 · 2010-06-05T13:50:32.083

0

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

edited Jun 05 '10 at 13:50

answered Jun 05 '10 at 07:28

El Yobo

14,823
5
60
78

Note that this only shows the dates where something happened; replace the subselect with the union with a table with all the dates in instead (e.g. http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) and you can show all the dates instead. – El Yobo Jun 05 '10 at 07:29
@EI Yobo: I am getting `NULL` instead of `Absent`, why? – Satish Jun 05 '10 at 08:13
Satish, what would you like to have? The bits that two lines that start with "ELSE NULL END" can be changed to be whatever you like, e.g. "ELSE 'Holiday' END" would give you Holiday instead of Absent. – El Yobo Jun 05 '10 at 13:48
If you just want it to be empty, change it to be "ELSE '' END" instead of "ELSE NULL END". – El Yobo Jun 05 '10 at 13:49
I've made this change for you in my answer. – El Yobo Jun 05 '10 at 13:50
You don't need to Coalesce `holiday_name`... the outer join will leave that blank. – colinmarc Jun 05 '10 at 15:14
Satish mentioned in his question that he didn't like the NULLs, so I modified the query to give him empty strings instead, as I did in the case statements. – El Yobo Jun 06 '10 at 01:59

Query in sql involving joins of two table

3 Answers3