'MySQL select current and previous column from the same table

I have the following columns in a table tbl:

    OrderID:     Parent:     Child:
    1              1           1
    2              2           1
    3              1           2
    4              1           1
    5              2           1

I wanted to get the current (OrderID,Parent,Child) and the LOWER (OrderID,Parent,Child) WHERE Parent and Child is equal to the current OrderID's Parent and Child.

So, for example if I have a query for OrderID=4 the result should be the following: 

OrderID:  Parent:    Child:
4            1         1
1            1         1

and if OrderID=5

OrderID:  Parent:    Child:
5           2           1
2           2           1

BTW, I can do this in PHP but I wanted to do it in a single query.

mysqlsql


Solution 1:[1]

SELECT * FROM FROM Orders WHERE  OrderID = @orderId
UNION ALL 
SELECT * FROM FROM Orders WHERE  Parent = @orderId

Solution 2:[2]

You could do something like this:

(SELECT OrderID, Parent, Child
FROM tbl
WHERE OrderID = 4)
UNION
(SELECT OrderID, Parent, Child
FROM tbl
WHERE Parent = (SELECT Parent FROM tbl WHERE OrderID = 4)
    AND Child = (SELECT Child FROM tbl WHERE OrderID = 4)
ORDER BY OrderID ASC LIMIT 1)

I'm not sure about the performance of this though...

Solution 3:[3]

This will do the trick:

SELECT *
FROM tbl
WHERE Parent = (SELECT Parent FROM tbl WHERE OrderID = 4) 
    AND Child = (SELECT Child FROM tbl WHERE OrderID = 4)

And inside php fetch the rows to an array, search your OrderID and get the one before it.

Solution 4:[4]

This works for me.

select B.* from 
    (select A.Parent,A.Child from `tbl` A
    where A.OrderID = 4) C
    inner join `tbl` B
on C.Parent = B.Parent
and C.Child = B.Child
order by B.OrderID desc

Solution 5:[5]

SELECT * FROM FROM Orders WHERE  OrderID = 4
UNION ALL 
SELECT * FROM FROM Orders WHERE  Parent = 4 ORDER BY OrderID DESC Limit 1

Also if you hav time look at this answer of mine will give you a good demonstration.

Finding free blocks of time in mysql and php?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mahmoud Gamal
Solution 2 user254875486
Solution 3
Solution 4 swemon
Solution 5 Community

Related Questions