One question again,
it works fine by executing in the sql editor, now I would like insert into my stored procedure: with mkat as ( SELECT node.id FROM tmatkat AS node, tmatkat AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = :smatkat ORDER BY parent.lft ) for select m.id, m.bezeichnung, m.lagermenge, m.bestellnr, r.raumname, m.lagerort, h.hersteller, m.bestellnr from tmaterial m left join thersteller h on (m.hersteller = h.id) left join traeume r on (m.raum = r.id) join tmaterial_kat e on m.id = e.matid join mkat f on e.katid = f.id group by m.id, m.bezeichnung, m.lagermenge, m.bestellnr, r.raumname, m.lagerort, h.hersteller, m.bestellnr into :a_lid, :bezeichnung, :lagermenge, :bestellnr, :raum, :lagerort, :hersteller, :sbestellnr do suspend; Where can be the error? "for" should be the problem. Thanks Von: [email protected] [mailto:[email protected]] Im Auftrag von checkmail Gesendet: Freitag, 14. Februar 2014 10:31 An: [email protected] Betreff: AW: [firebird-support] nested set . Hello, it works, please tell if I can make it better ;) with mkat as ( SELECT node.id FROM tmatkat AS node, tmatkat AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 190 ORDER BY parent.lft ) select d.bezeichnung, d.bestellnr from tmaterial d join tmaterial_kat e on d.id = e.matid join mkat f on e.katid = f.id group by d.bezeichnung, d.bestellnr Von: [email protected] [mailto:[email protected]] Im Auftrag von checkmail Gesendet: Freitag, 14. Februar 2014 09:04 An: <mailto:[email protected]> [email protected] Betreff: [firebird-support] nested set Hello, 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 parent.id = 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_Material 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 Olaf
