Subject nested set
Author checkmail



the following sql statement returns me all subcategories of material, starting with the category id 190 and returns it.


SELECT node.bez

FROM tmatkat AS node,

tmatkat AS parent

WHERE node.lft BETWEEN parent.lft+1 AND parent.rgt

AND = 190

ORDER BY parent.lft;


(at the time it returns me the name, in my case I will get the ID of the category)


Second, I have a table where I can set one or more category to an piece:


ID primary key


ID Category (includes left and right nested set)


Now I would get all the Material from the third table tmaterial, where the category matches.


For example:


The first select returns me categories 22, 32 and 56


Material ID 23 has assigned the Category IDs 22, 56 and 123

Material ID 24 has assigned the Category 56

Material ID 25 has assigned the Category 234


Now I should get the Material IDs 23 and 24 for one time (ID 23 not two times)


Can anybody helps?


Thank you and best regards