How can I turn a single row column into a scalar in SQL?

Question

This is sort of what I want to do, but MySQL doesn't seem to accept it.

SELECT Name, Content, Lft, Rht FROM Articles WHERE
    (Lft > (SELECT Lft FROM Articles WHERE idArticle = 1))
    AND WHERE
    (Rht < (SELECT Rht FROM Articles WHERE idArticle = 1));

I'm implementing the modified preorder tree transversal algorithm, and I want to get all the children of an Article using a single database query.

The Articles table looks like this:

Articles
+=============+===========+
| Type        | Name      |
+=============+===========+
| VARCHAR(45) | Name      |
+-------------+-----------+
| LONGTEXT    | Content   |
+-------------+-----------+
| INT         | Lft       |
+-------------+-----------+
| INT         | Rht       |
+-------------+-----------+
| INT         | idArticle |
+-------------+-----------+

idArticle is a primary key, and there are UNIQUE indexes on the Lft and Rht columns.

How might something like this be accomplished?

*Note: I'm currently using MySQL but I'd like to avoid any MySQL extensions where possible, because there are possible plans to move to another DB like Postgres or Oracle sometime in the future...

Answer 1

It's not being accepted because the WHERE keyword can only appear once in a query (outside of a subquery):

SELECT x.name,  
       x.content, 
       x.lft, 
       x.rht 
  FROM ARTICLES x
 WHERE EXISTS(SELECT NULL
                FROM ARTICLES y
               WHERE y.idarticle = 1
                 AND y.lft < x.lft)
  AND EXISTS(SELECT NULL
               FROM ARTICLES z
              WHERE z.idarticle = 1
                AND z.rht > x.rht)

Answer 2

Apparently it was just a simple syntax error (too many WHERE), but if you want to avoid the scalar subselect, you could also do a join (with the potential advantage that the row with idArticle 1 only needs to be loaded once, an optimization that your query planner may or may not be able to figure out by itself):

SELECT a.Name, a.Content, a.Lft, a.Rht FROM Articles a
   join Articles b on (b.idArticle =1 )
   where a.lft > b.lft and a.rht < b.rht;

Alternate equivalent

SELECT a.Name, a.Content, a.Lft, a.Rht 
  FROM Articles a
  join Articles b on a.lft > b.lft 
                 and a.rht < b.rht 
                 AND b.idArticle = 1 ;