I have to get "relevant" results from a table by joining it on another table and filtering the results on parent id and current id, this all goes fine until your current id is either the first or the last in a result set.
Imagine I have a result set that looks like this: 1,3,6,7,9,23,76 My current id is 3, I need to get the rows with id 1 and 3 back to my application, I managed to do that just fine, now the problem is that the application needs to make a circle.
So using the same result set as above, imagine my current id is 1 I need to get back 3 ( as the next result ) and 76 as my "previous" result.
I also managed to get that to work up to a certain extent, except for when your current id is the maximum, then something weird happens: it gets the next row from the given result set (in the example case if max = 76 then next is 1), but isn't able to retrieve it's previous one.
If I run the query separately, it works fine.
I am using ifnull, since if you don't have a row with an id that is smaller or bigger than the given id, it will return null and should execute the next statement.
Here is the SQL I'm currently using:
SELECT CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug, t1.id, t2.id, t3.id FROM table4 t4 left join table1 t1 on t1.id = t4.t1id left join table2 t2 on t2.id = t4.t2id left join table3 t3 on t3.id = t4.t3id WHERE t2.id IN (IFNULL((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id < 1424), (SELECT MAX(id) FROM table2 WHERE parent = 1046))) OR t2.id IN (IFNULL((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > 1424), (SELECT MIN(id) FROM table2 WHERE parent = 1046)))
This of course is pseudo code, but the only thing that changes relative to the actual DB is the table/column names.
EDIT: the IFNULLs do actually both return the right result when run separately, and the full query works if the current id is anything but the bigges
I hope you can point me in the right direction !