How to SELECT a sibling in an adjacency list type table? I'm trying to get the next sibling in a list according to an integer position column. Rows are siblings when they have the same parent_id.
Given that sibling foo.id = 192 and foo.position = 0, I want to get sibling bar that has a position of foo.position + 1
The below query returns the right result except that the AND a.position = 0+1 should not be hardcoded.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a 
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 192
)
AND a.position = 0+1
AND a.root_id = b.id;

The following query won't work of course but shows what I'm trying to do.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a 
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id, position as pos FROM tasks WHERE id = 192
)
AND a.position = pos+1
AND a.root_id = b.id;

Is this possible without first querying the needed information and saving it into a variable to use in a second query?
Would it be faster than saving the result of a &quot;pre-query&quot; to use in a second query?
EDIT:
Here's some test queries that set up a demo
Setup
CREATE TABLE tasks (
    id int unsigned NOT NULL AUTO_INCREMENT,
    root_id int unsigned,               /* top level task */
    parent_id int unsigned,             /* the task id that is the parent of this task */
    `title` varchar(255) NOT NULL,
    position int unsigned,              /* the index in the array that will contain this task */
    FOREIGN KEY (root_id) REFERENCES tasks(id),
    FOREIGN KEY (parent_id) REFERENCES tasks(id),
    PRIMARY KEY(id)
);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES 
(NULL, NULL, 'Foo Project', 0),
(NULL, NULL, 'Bar Project', 1),
(NULL, NULL, 'Baz Project', 2);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES 
(1, 1, 'Foo Child A', 0),
(1, 1, 'Foo Child B', 1),
(2, 2, 'Bar Child A', 0);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES 
(2, 6, 'Bar Child A Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 2', 1);

Query for needed info
The following query is suboptimal but retrieves the info needed.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a 
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 8
)
AND a.position = (SELECT position FROM tasks WHERE id = 8)+1
AND a.root_id = b.id;
It seems you need

SELECT t1.title taskTitle, 
       t2.title projectTitle
FROM tasks t1
JOIN tasks t2 ON t1.parent_id = t2.parent_id
             AND t1.position + 1 = t2.position
WHERE t1.id = 192