CODEWITHSUNDEEP
mysqlstored-proceduresjoinviewsubquery
Atif Naseem
Atif Naseem

Reputation: 25

MySQL Joining multiple select satement containing multiple values for View

I have a Table having all IN detail

+----------+------------+-----------+
| staff_id | date       | time      | 
+----------+------------+-----------+
|    1     | 2015-02-20 |    07:00  |  
|    2     | 2015-02-20 |    07:01  |  
|    3     | 2015-02-20 |    07:05  |  
|    1     | 2015-02-20 |    07:02  |  
|    1     | 2015-02-20 |    07:04  |  
+----------+--------------+---------+

another Table having all OUT detail

+----------+------------+-----------+
| staff_id | date       | time      | 
+----------+------------+-----------+
|    1     | 2015-02-20 |    13:00  |  
|    2     | 2015-02-20 |    13:45  |  
|    3     | 2015-02-20 |    13:45  |  
|    1     | 2015-02-20 |    13:47  |  
|    1     | 2015-02-20 |    13:48  |  
+----------+--------------+---------+

What required result is Time In, min val and Time Out, max value

+----------+------------+-----------+
| staff_id | date       | time IN   | Time Out
+----------+------------+-----------+
|    1     | 2015-02-20 |    07:00  | 13:48
|    2     | 2015-02-20 |    07:01  | 13:45

What Im doing is

SELECT *
FROM
  (SELECT sai.staff_id AS staff_id_in,
          sai.date AS date_in,
          sai.time AS time_in,
          sai.ip4 AS ip4_in,
          sai.location_id AS location_id_in,
          '1' AS atd_in
   FROM staff_attendance_in sai
   ORDER BY staff_id ASC, time ASC) AS sub
GROUP BY staff_id_in,
         date_in
UNION
SELECT *
FROM
  (SELECT sao.staff_id AS staff_id_out,
          sao.date AS date_out,
          sao.time AS time_out,
          sao.ip4 AS ip4_out,
          sao.location_id AS location_id_out,
          '2' AS atd_out
   FROM staff_attendance_out sao
   ORDER BY time DESC) AS sub
GROUP BY staff_id_out,
         date_out

but I am not able to generate view from the query... neither join

Upvotes: 0

Views: 177

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try this:-

SELECT I.staff_id, I.date, MIN(I.time_IN), MAX(O.Time Out)
FROM IN I JOIN OUT O
ON I.staff_id = O.staff_id
GROUP BY I.staff_id, I.date;

I think this can help you.

Upvotes: 1

Related Questions