Help needed in writing sql query

Question

how can i write a query for this? I'm not an expert to write this much of complex queries in sql. I forgot to mention bid 3, 4 here

Thank you, Jestges

Answer 1

SELECT
   a.bid, a.time checkin, ISNULL(b.time, '') checkout
FROM
   myTable a
LEFT JOIN
   myTable b ON a.bid = b.bid AND b.type = 'Check Out'
WHERE
   a.type = 'Check In'
ORDER BY
   a.time

EDIT

In response to your comment, seeing as there are several records of the same bid, and you only want one record per bid in your output, you need to specify which records you want in your output. When there are more than one, how do you decide what value to pick? If you always want the earliest checkin and the latest checkout, you might do something like this:

SELECT
    a.bid,
    MIN(a.time) checkin,
    ISNULL((SELECT
              MAX(time)
           FROM
              myTable
           WHERE
              bid = a.bid
              AND type = 'Check Out'), '') checkout
FROM
    myTable a
WHERE
    a.type = 'Check In'
GROUP BY
    a.bid

If that's not exactly what you want; adjust the use of MIN and MAX to fit your needs.

Answer 2

This will find the minimum check in and check out times for each bid id.

select bid, 
(select min(time) from table b where a.bid = b.bid and type = "Check In") as CheckIn,
(select min(time) from table c where a.bid = c.bid and type = "Check Out") as CheckOut
from table c
group by c.bid

Answer 3

I think you're looking for something like this:

SELECT 
    `bid`, 
    MAX(IF(`type` = 'Check In', `time`, NULL)) AS `CheckIn`, 
    MAX(IF(`type` = 'Check Out', `time`, NULL)) AS `CheckOut` 
FROM `yourtable` 
GROUP BY `bid`

Answer 4

Based on David's query, eliminating duplicate bids and restricting by check type.

SELECT
   a.bid, min(a.time) checkin, ISNULL(min(b.time), '') checkout
FROM
   myTable a
LEFT JOIN
   myTable b ON a.bid = b.bid
WHERE
   a.type = "Check In"
   AND
   b.type = "Check Out"
GROUP BY
   a.bid
ORDER BY
   a.time