HI everybody, im stucked only at that:

focusing on joomla! 1.6 extension, im using groups.

for individuals who dont used groups, they work such as this:

you produce a category (title, description, parent) and it is saved within the groups table.

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

id and parent_id i do not need to describe.

level: is the amount of the course, being ROOT, so a young child of root could be level 1 along with a child of an amount one be level 2 , etc

lft and rgt are values accustomed to wrap all groups sub groups (helpful when you need I.E. group of level 1 children's as much as level 2)

my table includes a catid column, that indicates the id from the corresponding category.

things i need is to buy all children's from that category only one level lower,

I.E. if my catid indicates an amount 2 category i will have to get all groups whose level is 3 as well as their parent's id = catid

first got it?

how do you write the query?

i've something similar to this (the totally compiled by a PHP script)

let us try with an amount 1 category

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

how do i compare against all the subquerie's values?

THNX :D

You will get better performance with no 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 overlooked the level = 2 purposely, because this is a redundant information. If your category includes a parent_id=1, then it's level=2.

You could utilize IN.

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