Q:

Join two Tables based on id order by time

I need to combine two tables and order by the two tables date..
Medicine
| Id | Patientid | Name | Quantity |  medicine name | Takingtype | Epoch     |
===============================================================================
|  1 |       12  | raja | 20       | Ampicillin     |    1-1-1   | 1420446675|

Surgical
| Id | Patientid | Name | medicine name | Quantity | Epoch      |
=======================================================================
|  1 |       12  | raja | Mucinex oral  | 10       | 1420446675 |

Can any one help me?
The Result should be like  this,
|s.no| Date     | Name | Quantity  |  medicine name | Takingtype |
=================================================================
|  1 | 1-5-2015 | raja |  20       | Ampicillin     |    1-1-1   |
|  2 | 1-5-2015 | raja |  10       |Mucinex oral    |            |

Please help me join the tables like the result showed above. I'm looking for a mysql query..
(I have changed the epoch time in to date in php..)

A:

If I understand your question right, then what you want is not a join, it's a union.
select * from (
    select Name, Quantity, MedicineName, Epoch, Takingtype From Medicine
        union all 
    select Name, Quantity, MedicineName, Epoch, NULL From Surgical
) x order by epoch;

I also assume that you want s.no to be a column that contains row ordinals. To my best knowledge you cannot really achieve that in MySQL without a temporary table (experts might correct me here).
Finally, if your datetime is indeed a time from the epoch, you need to use a FROM_UNIXTIMESTAMP builtin, something along the lines of
select * from (
    select FROM_UNIXTIMESTAMP(Epoch) AS Date, Name, Quantity, MedicineName, Epoch, Takingtype From Medicine 
        union all 
    select FROM_UNIXTIMESTAMP(Epoch) AS Date, Name, Quantity, MedicineName, Epoch, NULL From Surgical
) x order by epoch;

