MySQL match multiple results using WHERE from subquery

I'm stuck on this: I'm working on a Joomla! 1.6 extension and using categories.

For those who haven't used categories, they work like this:

You create a category (name, description, parent) and its stored in the categories table.

|  id  |  parent_id  |  lft  |  rgt  |  level  |  etc  |
--------------------------------------------------------
|   1  |      0      |   -   |   -   |    1    |
|   2  |      1      |   -   |   -   |    2    |

id and parent_id, I don't need to explain.

level: is the level of the category, being 0 ROOT, so a child of root would be level 1 and a child of a level one be level 2 , etc

lft and rgt are values used to wrap all categories sub categories (useful when you need to get I.E. category of level 1 children up to level 2)

My table has a catid column, that points to the ID of the corresponding category.

What I need is to get all children from that category but only one level lower,

I.E. if my catid points to a level 2 category I would need to get all categories whose level is 3 and their parent's id = catid

How do I write the query?

I have something like this (the query is written by a PHP script)/

Let's try with a level 1 category

SELECT * 
FROM my_table 
WHERE catid = 1 
   OR catid = (SELECT catid 
       开发者_如何学JAVA        FROM categories 
               WHERE level = 2 AND parent_id = 1)

How can I compare against all of the subquery's values?

---

You'll get better performance without a subquery:

SELECT t.*
FROM my_table AS t
INNER JOIN categories AS c ON t.catid = c.id
WHERE c.id = 1
OR c.parent_id = 1

I omitted the level = 2 on purpose, as this is a redundant information. If a category has a parent_id=1, then its level=2.

---

You could use IN.

SELECT *
FROM   my_table
WHERE  catid = 1
        OR catid IN (SELECT catid
                     FROM   categories
                     WHERE  level = 2
                            AND parent_id = 1)