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?
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
SELECT * FROM my_table WHERE catid = 1 OR catid IN (SELECT catid FROM categories WHERE level = 2 AND parent_id = 1)