Forum Moderators: open
I have a table, eg.
id ¦ name ¦ description ¦ sku ¦ categoryid
------------------------------------------
1 ¦ item1 ¦ desc1 ¦ 1111 ¦ 1
2 ¦ item2 ¦ desc2 ¦ 2222 ¦ 1
3 ¦ item3 ¦ desc3 ¦ 3333 ¦ 2
4 ¦ item4 ¦ desc4 ¦ 4444 ¦ 1
5 ¦ item5 ¦ desc5 ¦ 5555 ¦ 1
6 ¦ item6 ¦ desc6 ¦ 6666 ¦ 1
say my "current" item is #4, which is in category 1.
What I want to select from the database is - from items from the same category - the one previous to 4, and the one after 4, as ordered by their id.
That means the result I would like is:
2 ¦ item2 ¦ desc2 ¦ 2222 ¦ 1
5 ¦ item5 ¦ desc5 ¦ 5555 ¦ 1
Note that item #3 is not in the same category, so it is not returned. #2 is.
Mind that the id may not be sequential, so I can't fudge it with (id - 1) or (id + 1)
How can I do this?
select * FROM tablename WHERE categoryid = 1 AND itemid < 4 order by itemid desc limit 1
select * FROM tablename WHERE categoryid = 1 AND itemid > 4 order by itemid limit 1
(SELECT * FROM tablename WHERE categoryid = 1 AND itemid < 4 ORDER BY itemid DESC LIMIT 1)
UNION
(SELECT * FROM tablename WHERE categoryid = 1 AND itemid > 4 ORDER BY itemid LIMIT 1)
