Get previous and next row from result set help
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 &lt; t4.t2id),
                    (SELECT MAX(id) FROM table2 WHERE parent = 1046))
    UNION
    SELECT COALESCE((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id &gt; t4.t2id),
                    (SELECT MIN(id) FROM table2 WHERE parent = 1046)))
left join table3 t3 on t3.id = t4.t3id
where t4.t2id = 1424