Join operation on two tables retrieving dates

Question

My first query, retrieving date and hours worked from work_details of a given employee number in a given date.

SELECT date,
       SEC_TO_TIME( SUM( TIME_TO_SEC( `total_hours` ) ) ) AS total
FROM `work_details`
WHERE employee_id='28'
  and date between  '2012-02-01'
  and '2012-02-29'
GROUP BY DATE ORDER BY DATE

and the Second query retrieving date from table holy_date:

SELECT holy_date
from holiday
where holy_date between '2012-02-01' and '2012-02-29'

I need to combine results of the two queries in the correct date order. I tried union operation,but dint get result.

How can I do it?

What exactly are you trying to do? You can't use union unless you have the same number of columns in both queries. — jle, Jun 25 '12 at 14:04
how do you combine two result sets that have unequal number of fields? — nawfal, Jun 25 '12 at 14:05
Yes, i understood that when i tried with union!!! Im trying to combine working days from work_details table and holidays entries from holiday table, both are of date type in sql. Im required to get the total working hours of a given day, -> SEC_TO_TIME( SUM( TIME_TO_SEC( `total_hours` ) ) ) AS total implies this!! — Jojo George, Jun 25 '12 at 14:08
yep! But there is no way, ? I'm using this combined result set for finding the leaves taken by an employee, days which is not coming as holidays and working days can taken as leaves , no? and if the hours worked in a day is less than 4 hours , i need to count that day as half day leave , thats y i require hours too! — Jojo George, Jun 25 '12 at 14:12
@JojoGeorge and since on holidays none bothers about working hours, u can neglect that field as nulls :) — nawfal, Jun 25 '12 at 14:14
@JojoGeorge Are you trying to get date, total, holy_date in one row? What are the fields in holyday table? — Pablo Claus, Jun 25 '12 at 14:20
possible duplicate of [What is the difference between Join and Union?](http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union) — Mohammed Azharuddin Shaikh, Jun 26 '12 at 09:48

nawfal · Accepted Answer · 2012-06-27T14:29:52.460

1

There are a few ways to achieve what you want.

This is not the documented way of doing it. But this should work.

SELECT   date, total
FROM 
         (
          SELECT   date, SEC_TO_TIME( SUM( TIME_TO_SEC( `total_hours` ) ) ) AS total 
          FROM     `work_details` 
          WHERE    employee_id='28' AND date BETWEEN  '2012-02-01' AND '2012-02-29' 
          GROUP BY date 

          UNION ALL

          (  
           SELECT holy_date AS date, NULL AS total 
           FROM   holiday 
           WHERE holy_date BETWEEN '2012-02-01' AND '2012-02-29'
          )
         ) AS t
GROUP BY date
ORDER BY date

edited Jun 27 '12 at 14:29

answered Jun 25 '12 at 14:11

nawfal

70,104
56
326
368

Exactly thats what i wanted :) Thanks Lot yaar :) – Jojo George Jun 25 '12 at 14:17
I have a doubt regarding this , when i use UNION ALL i get duplicate values . that is, if same date is in both queries,then answer shows both dates as separate entries, but i need to have only distinct dates in my answer , what to do? – Jojo George Jun 27 '12 at 13:44
@JojoGeorge why not ask it straight? :) – nawfal Jun 27 '12 at 13:48
straight means chat? im not used with this site,so kindly tell me how can i chat with u? – Jojo George Jun 27 '12 at 13:50
@JojoGeorge I dont think u have enough reputation to chat. By straight, I meant without an introduction, why dont you ask the question directly. Since you edited your comment, let me read it – nawfal Jun 27 '12 at 13:56
see this example, 2012-01-11 08:00:00 (result from table holiday,i NULL changed it to 08:00:00 as for default) 2012-01-11 07:53:17(answer from table workdetails),if same date ,how to give priority to latter answer? i mean answer from table workdetails – Jojo George Jun 27 '12 at 13:56
2012-01-11 08:00:00 2012-01-11 07:53:17 , same date as two different dates i got when i used UNION ALL, i need only 2012-01-11 07:53:17 – Jojo George Jun 27 '12 at 13:59
@JojoGeorge oh shit. Sorry I missed something trivial. Let me edit my answer. wait. and then see. But mind u there are many ways to achieve this. Is performance critical here? – nawfal Jun 27 '12 at 14:03
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13115/discussion-between-jojo-george-and-nawfal) – Jojo George Jun 27 '12 at 14:03
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY date ORDER BY date ' at line 12 shows this error! – Jojo George Jun 27 '12 at 14:22

Join operation on two tables retrieving dates

1 Answers1