Get previous and next row from result set help

Question

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 !

Answer 1

Instead of 1424, perhaps you should use t4.t2id. Instead of 1046, if the parent exists in table4 or any of the others, use that link instead of the constant.

You could also use a single IN, and a UNION of both queries.

Finally, if you use a filter condition in WHERE on a LEFT JOINed table, that defeats the purpose of a LEFT JOIN and might result in bugs. Either use JOIN or move the condition to the ON part of that LEFT JOIN. In this case, the condition in the LEFT JOIN was different than the one in WHERE for the same field (t2.id), so it could never work.

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 IN (
    SELECT COALESCE((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id < t4.t2id),
                    (SELECT MAX(id) FROM table2 WHERE parent = 1046))
    UNION
    SELECT COALESCE((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > t4.t2id),
                    (SELECT MIN(id) FROM table2 WHERE parent = 1046)))
left join table3 t3 on t3.id = t4.t3id
where t4.t2id = 1424