0

I have on table which store enter and exit of people in multi rows like:

EorL 1 = enter

EorL 2 = exit or leave

id   |  date_    |time    |EorL|
1    |  150201   | 09:14  | 1  |
2    |  150201   | 09:14  | 1  |
3    |  150201   | 09:15  | 1  |
1    |  150201   | 16:07  | 2  |
2    |  150201   | 16:07  | 2  |
3    |  150201   | 17:15  | 2  |

how can combine and get:

id    |   date_     |enter     |leave
1     |  150201     |09:14     |16:07
2     |  150201     |09:14     |16:07
3     |  150201     |09:15     |17:15

thank you in advance

1

This seems like a simple self join:

SELECT 
    enter.id,
    enter.date_ 
    enter.time AS enter_time
    leave.time AS leave_time
FROM 
    tablename AS enter
  JOIN
    tablename AS leave
  ON  enter.id = leave.id
  AND enter.date_ = leave.date_
  AND leave.EorL = 2 
WHERE
    enter.EorL = 1 ;

The query assumes there are (max) 2 rows for any id and date_.

If there are cases where people have entered without leaving, use LEFT join instead. If there are people who have left without entering, use RIGHT join. If there might be both cases, use FULL join. For RIGHT and FULL, you'll have to adjust the SELECT list, using leave.id, leave.date_ and COALESCE(enter.id, leave.id), COALESCE(enter.date_, leave.date_).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.