CODEWITHSUNDEEP
mysqlsql
DennisFrea
DennisFrea

Reputation: 1122

Mysql Query to Merge Two Condition Into One Row

I just get confused. Already tried to search this whole site or google but didn't find the 'nearest' solution.

Ok let's say I have this table structure.

id date       finger_id finger_time is_enter
1  2017-03-30  2          09:00       1
2  2017-03-30  2          17:13       0
3  2017-03-31  4          09:10       1
4  2017-03-31  3          09:01       1
5. 2017-03-31  3          17:00       0

I want to make the table to be like below.

date       finger_id  enter_time  exit_time
2017-03-30   2        09:00       17:13
2017-03-30   4        09:10
2017-03-31   3        09:10       17:00

I have made sql statement but it turns like this.

date       finger_id  enter_time   exit_time
2017-03-30  2         09:00
2017-03-30  2                       17:13
2017-03-31  4         09:10
2017-03-31  3         09:01
2017-03-31  3                       17:00

I just want to know how to merge the is_enter 1 with is_enter 0 on the same date by the finger_id column.

Here's my sql query for the reference.

SELECT * 
FROM `tbl_fingerprint` 
    LEFT JOIN `tbl_employee` ON `tbl_employee`.`fingerprint_id`=`tbl_fingerprint`.`fingerprint_id` 
    LEFT JOIN `tbl_position` ON `tbl_position`.`position_id`=`tbl_employee`.`position_id` 
WHERE `fingerprint_date` >= '2017-03-01' 
AND `fingerprint_date` <= '2017-04-01' 
GROUP BY `tbl_fingerprint`.`fingerprint_id`, 
         `tbl_fingerprint`.`fingerprint_date`, 
         `tbl_fingerprint`.`is_enter` 
ORDER BY `fingerprint_date` ASC LIMIT 30

Thanks for your help guys.

Upvotes: 1

Views: 731

Answers (4)

vims liu
vims liu

Reputation: 643

SELECT a1.*, a3.time as time_out FROM attendance as a1
INNER JOIN (SELECT MIN(id) as id FROM attendance where is_enter = '1' group by date, f_id ) as a2
ON a2.id = a1.id
LEFT JOIN attendance as a3 ON a3.date = a1.date AND a1.f_id = a3.f_id and a3.is_enter = '0'

you may need to cast the date to not include the time portion or to char with the yyyy-mm-dd format

Upvotes: 0

Rakesh Kumar
Rakesh Kumar

Reputation: 4420

Try This (This will work if finger_time is of time type):-

SELECT date, finger_id, min(finger_time) enter_time, if (min(finger_time) = max(finger_time), null, max(finger_time)) exit_time FROM xyz group by finger_id, date

Upvotes: 0

Ferico Samuel
Ferico Samuel

Reputation: 194

SELECT * FROM `tbl_fingerprint`
LEFT JOIN `tbl_employee` ON `tbl_employee`.`fingerprint_id`=`tbl_fingerprint`.`fingerprint_id`
LEFT JOIN `tbl_position` ON `tbl_position`.`position_id`=`tbl_employee`.`position_id`
LEFT JOIN (SELECT * FROM tbl_fingerprint WHERE is_enter = 0) a
    ON a.finger_id = tbl_fingerprint.finger_id AND a.date = tbl_fingerprint.date
WHERE `fingerprint_date` >= '2017-03-01' AND `fingerprint_date` <= '2017-04-01' AND tbl_fingerprint.is_enter = 1
GROUP BY `tbl_fingerprint`.`fingerprint_id`, `tbl_fingerprint`.`fingerprint_date`, `tbl_fingerprint`.`is_enter`
ORDER BY `fingerprint_date` ASC LIMIT 30

Upvotes: 0

Shadow
Shadow

Reputation: 34231

You can do a group by date and finger_id fields and use conditional expression (case or if()) within an aggregate function to get the expected outcome. The conditional statements within the aggregate function make sure that they return value only if the right value is set in is_enter field. I leave out the employee details, since those do not form part of your question:

SELECT date, fingerprint_id, max(if(is_enter=1,finger_time,null) as enter_time, max(if(is_enter=0,finger_time,null) as exit_time
FROM `tbl_fingerprint` 
WHERE `fingerprint_date` >= '2017-03-01' 
AND `fingerprint_date` <= '2017-04-01' 
GROUP BY `tbl_fingerprint`.`fingerprint_id`, 
         `tbl_fingerprint`.`fingerprint_date`,  
ORDER BY `fingerprint_date` ASC LIMIT 30

Upvotes: 1

Related Questions