MySQL query (sequential records...)

Question

I have a MySQL with the following table structure:

table mt_events
id - record id
doc_id - document id
event_id - event id
date - event date
user - user

The problem is that I need a report of all the documents that had an event 3 with a following event 4, which would seem quite an easy simple JOIN, except that one document can have MULTIPLE event 3, which might or might not be followed by event 4... like this:

1 3a 4a 11 12 8 3b 4b 3c 4c 3 17 5

So far I've gotten to :

SELECT ev.date AS Event1, ev.user AS User1, ev2.date AS Event2, ev2.user AS User2
FROM  `mt_events` ev
JOIN mt_events ev2 ON ev2.doc_id = ev.doc_id AND ev2.event = 4 AND ev2.date > ev.date
WHERE ev.event = 3 AND ev.date LIKE '2013-03%'

But that doesn't produce required results -

3a 4a
3b 4b
3c 4c

and gives -

3a 4a 
3a 4b
3b 4b
3a 4c
3b 4c
3c 4c

Any help would be appreciated...

Could you explain what the values 1 3a 4a etc are? Is 3 the event code and a the document code? — Vince Bowdren
– Vince Bowdren, Commented Apr 29, 2013 at 10:44
@vincebowdren: If I understand correctly, 3 is the event number field (called event) and a is a symbolic representation of date. — user359040
– user359040, Commented Apr 29, 2013 at 10:51
Just a symbolic representation of order of events for one document and how they should be paired. — GTMeteor
– GTMeteor, Commented Apr 29, 2013 at 10:59

user359040 · Accepted Answer · 2013-04-29 10:36:04Z

2

Try:

SELECT ev.date AS Event1, ev.user AS User1, ev2.date AS Event2, ev2.user AS User2
FROM  `mt_events` ev
JOIN mt_events ev2 ON ev2.doc_id = ev.doc_id AND ev2.event = 4 AND ev2.date =
    (select min(el.date) from `mt_events` el
     where el.doc_id = ev.doc_id AND el.event = 4 AND el.date > ev.date)
WHERE ev.event = 3 AND ev.date LIKE '2013-03%'

answered Apr 29, 2013 at 10:36

user359040

Sign up to request clarification or add additional context in comments.

1 Comment

GTMeteor Over a year ago

Worked like a charm. Thank you. I was unaware that you could do selects in comparison parts...

Nebril · Accepted Answer · 2013-04-29 10:28:01Z

0

Try moving the ev2.event = 4 AND ev2.date > ev.date to WHERE clause. It seems more appropriate.

Moreover, you don't seem to have the information about direct precedence in the table - only the order of them (date). So the query you are trying to perform will return the row for each pair of events 3 and 4 where 3 happened before 4 (but not neccesarily immediately before).

You should probably use a stored procedure or some other way to iterate over the result and filter out the results that do not meet your conditions.

answered Apr 29, 2013 at 10:28

Nebril

3,3211 gold badge35 silver badges51 bronze badges

Collectives™ on Stack Overflow

MySQL query (sequential records...)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related