MySQL select current and previous column from the same table

Question

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.

What should happen if order 3 is requested? What should happen if there are more than 2 matches? — Mark Byers, Sep 18 '12 at 10:20
Only one LOWER OrderID should display. If no LOWER OrderID, so just display the current OrderID. — hodl, Sep 18 '12 at 10:21

Maringo · Answer 1 · 2012-09-18T10:29:32.150

0

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.

edited Sep 18 '12 at 10:29

answered Sep 18 '12 at 10:19

Maringo

109
4

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

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?

edited May 23 '17 at 11:49

Community

1
1

answered Sep 18 '12 at 10:20

Muhammad Raheel

19,823
7
67
103

score 0 · Answer 3 · answered Sep 18 '12 at 10:20

0

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

answered Sep 18 '12 at 10:20

Mahmoud Gamal

78,257
17
139
164

score 0 · Answer 4 · answered Sep 18 '12 at 10:20

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...

score 0 · Answer 5 · answered Sep 18 '12 at 10:31

0

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

answered Sep 18 '12 at 10:31

swemon

5,840
4
32
54

score 0 · Accepted Answer · answered Sep 18 '12 at 11:27

Okay, thanks all for your replies but I got my own answer.

SELECT a.OrderID,a.Parent,a.Child FROM tbl a 
JOIN (SELECT OrderID,Parent,Child FROM tbl WHERE OrderID={$OrderID}) b 
ON a.Parent=b.Parent 
AND a.Child=b.Child 
WHERE a.OrderId<=b.OrderID 
ORDER BY OrderID 
DESC LIMIT 2

MySQL select current and previous column from the same table

6 Answers6