Mysql Inner Join Query

Question

I am new to Mysql query.

I have 2 tables (tbl1, tbl2)

I need to join two tables with ascending order start_time and filter the date and emp_id

Here the table fields are different (start time → time, end time → wait time)

tbl-1:

id      start-time      end-time        date            emp_id

1       09:00:00        09:00:59        2014-05-14        1

2       10:00:00        10:00:59        2014-05-14        1

3       12:00:00        12:00:59        2014-05-14        1

4       14:00:00        14:00:59        2014-05-14        1

5       16:00:00        17:00:59        2014-05-13        1


tbl-2:

id      in-time         out-time        date            emp_id

1       11:00:00        11:00:59        2014-05-14        1

2       13:00:00        13:00:59        2014-05-14        1

3       15:00:00        15:00:59        2014-05-14        1

4       18:00:00        19:00:59        2014-05-14        2

5       20:00:00        20:00:59        2014-05-15        1


filterd by date, emp_id ordered by date

result-tbl:

id      start-time      end-time        date            emp_id

1       09:00:00        09:00:59        2014-05-14        1

2       10:00:00        10:00:59        2014-05-14        1

3       11:00:00        11:00:59        2014-05-14        1

4       12:00:00        12:00:59        2014-05-14        1

5       13:00:00        13:00:59        2014-05-14        1

6       14:00:00        14:00:59        2014-05-14        1

7       15:00:00        15:00:59        2014-05-14        1

Answer 1

I do not think you want to join. I think you want union allwith the ids reinstall method . Sort of:

select (@rn := @rn + 1) as id, intime as starttime, outtime as outtime, emp_id
from ((select * from tbl1)
      union all
      (select * from tbl2)
     ) t cross join
     (select @rn := 0) var
where emp_id = 1 and
      intime <= '18:00:00'
order by intime;

Answer 2

Do you want to have a record pool?

select in_time, out_time, date, emp_id from tbl1
union all
select star_time, end_time, date, emp_id from tbl2
order by in_time, out_time, date, emp_id;

EDIT: plus filter:

select in_time, out_time, date, emp_id from tbl1
where emp_id = 1 and date = '2014-05-14'
union all
select star_time, end_time, date, emp_id from tbl2
where emp_id = 1 and date = '2014-05-14'
order by in_time, out_time;

Answer 3

This may solve your problem.

select *
from (
        select id,star_time,end_time,date,emp_id
        from tbl1
        where [date-filter-condition]
        union all
        select id,in_time as star_time,out_time end_time,date,emp_id
        from tbl2
        where [date-filter-condition]
) tmp
where tmp.date==[some-date-filter] and tmp.emp_id=[some-emp-id-filter]
order by tmp.date