CODEWITHSUNDEEP
mysqlsqldatabase
Scott Rowley
Scott Rowley

Reputation: 484

SQL > Select multiple rows for single result

SQL Table

I have the above table, what I need to do is, for example based off this image, have a single row returned that would show:

Ticket ID |       Created       |       Assigned      | Time Difference  
    2     | 2015-05-31 09:18:44 | 2015-06-03 10:05:00 | $MATH_RESULT

This image only shows one example but I'd like to have it return each ticket that has both an 'assigned' time and a 'created' time.

I'm not DBA and this is a bit beyond my limited knowledge. Can someone help me on how I can accomplish this?

Upvotes: 0

Views: 62

Answers (2)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/cf498/3

SELECT t.ticket_id,t.timestamp as created, t1.timestamp as assigned, (t1.timestamp - t.timestamp) as delay
FROM table1 t
LEFT JOIN table1 t1
ON t.ticket_id = t1.ticket_id
  AND t1.state = 'assigned'
WHERE t.ticket_id = 2
   and t.state = 'created'

or if you need list of all tickets

SELECT t.ticket_id,t.timestamp as created, t1.timestamp as assigned, (t1.timestamp - t.timestamp) as delay
FROM table1 t
LEFT JOIN table1 t1
ON t.ticket_id = t1.ticket_id
  AND t1.state = 'assigned'
WHERE t.state = 'created'

Upvotes: 1

Andomar
Andomar

Reputation: 238296

You can use a join to find the row with state "assigned" for the row in state "created":

select  c.ticket_id
,       c.timestamp
,       a.timestamp
,       datediff(a.timestamp, c.timestamp)
from    ticket c
join    ticket a
on      c.ticket_id = a.ticket_id
where   c.state = 'created'
        and a.state = 'assigned'

Upvotes: 1

Related Questions