Selecting same column twice in the same query with different conditions

[sheronkitchilan]

Hi All,

As shown in the image, am having the following columns in the database, and i have to get in(F1) and out (F2) of each employee in the following format.

Expected Outcome :

IN = mode_T (F1)
OUT = mode_T(F2)
date, name,employee_id ,IN,OUT
(2018-01-01,e1,time1,time2)
(2018-01-01,e2,time1,time2)
(2018-01-01,e3,time1,time2)

[DaveMaxwell]

Will you have only one F1 and one F2 per day? If so, then you can do it with a sub-query

SELECT date
     , employee_id
     , i.time AS TimeIn
     , o.time AS TimeOut
  FROM tablename i
  JOIN (SELECT date
             , employee_id
             , time
          FROM tablename
         WHERE mode_t = 'F2') o ON i.employee_id = o.employee_id AND o.date = i.date
  WHERE mode_t = 'F1'

If you can have multiple records per day…good luck. That takes a whole other level of sqlFU which takes a whole lot longer to get right.

[r937]

FYI first two columns in your SELECT clause are ambiguous

also, you don’t actually need to use a subquery…

SELECT i.date , i.employee_id , i.time AS TimeIn , o.time AS TimeOut FROM tablename AS i INNER JOIN tablename AS o ON o.employee_id = i.employee_id AND o.date = i.date AND o.mode_t = 'F2' WHERE i.mode_t = 'F1'