CODEWITHSUNDEEP
mysqljoinunion
Jhonatan Sandoval
Jhonatan Sandoval

Reputation: 1293

MySQL - Combine with SELECT two tables

I have these tables:

reg_movs :

regmovs_id      id_car             date               status    
   1              1         2014-02-02 16:00:00         0
   2              1         2014-02-02 16:12:00         5         
   3              2         2014-02-02 16:22:03         0
   4              2         2014-02-02 16:45:14         5

service_detail :

id          id_Car                date_updated           price     
1             1               2014-02-02 16:12:00        45.00    
2             2               2014-02-02 16:45:14        30.00

I mean, when status=0, it will set the start time in my SELECT QUERY. And, when status=5, it will the endtime.

How can i make this query like this:

id_car     date         start         end                 price
  1     2014-02-02     16:00:00    16:12:00               45.00
  2     201-02-02      16:22:03    16:46:14               30.00

Upvotes: 0

Views: 40

Answers (1)

Riz
Riz

Reputation: 1131

You will need to first do a self join on reg_movs to merge two rows into a single row. Then you need inner join this with service_detail to pick the price of car. Below is a sample query based on the information provided for the schema.

SELECT t1.id_car
    , DATE_FORMAT(t1.date, '%Y-%m-%d') AS date
    , DATE_FORMAT(t1.date, '%T') AS start
    , DATE_FORMAT(t2.date, '%T') AS end
    , service_detail.price 
FROM reg_movs AS t1 
    inner join reg_movs AS t2 on t1.id_car=t2.id_car AND t1.status=0 AND t2.status=5
    inner join service_detail ON t1.id_car=service_detail .id_car

You may need to change the query to use left join if you want to display records that don't have end time (i.e. they have only one record in the table with status 0)

Upvotes: 1

Related Questions