MySQL : find a sequential pattern appears on multiple rows

Question

I am trying to find rows in a table that follows a specific pattern. For example, there is a table with three columns, ID, PRODUCT_ID and ACTION respectively.

ID   PRODUCT_ID   ACTION

1    A001                 ABC
2    A001                 DHI
3    A001                 FGH
4    B111                 FGH
5    A001                 JKL
6    A001                 ZAB

I would like to retrieve the records having 3 actions in order, ABC-FGH-ZAB for same PRODUCT_ID. From above example, I would like to get the rows with IDs, 1, 3, and 6 as an answer and the row with ID 4 should be ignored since it has a different PRODUCT_ID. How do I formulate a query to have a result set like below on MySQL?

ID   PRODUCT_ID   ACTION

1    A001                 ABC
3    A001                 FGH
6    A001                 ZAB

I try not to use a nested query unless I have to for a performance reason . The order of ACTION is important, so FGH-ZAB-ABC should not be retrieved.

Thanks in advance

Answer 1

SELECT
    yt1.ID AS First,
    yt2.ID AS Second,
    yt3.ID AS Third
FROM
    YourTable yt1 
    JOIN YourTable yt2 
        ON yt2.ID > yt1.ID 
        AND yt2.Action = 'FGH' 
        AND yt2.Product_ID = y1.Product_ID
    JOIN YourTable yt3 
        ON yt3.ID > yt2.ID 
        AND yt3.Action = 'ZAB'
        AND yt3.Product_ID = y1.Product_ID
WHERE
    yt1.Action = 'ABC'