| Subject | AW: [firebird-support] nested set |
|---|---|
| Author | checkmail |
| Post date | 2014-02-14T09:58:07Z |
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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von checkmail
Gesendet: Freitag, 14. Februar 2014 10:31
An: firebird-support@yahoogroups.com
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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von checkmail
Gesendet: Freitag, 14. Februar 2014 09:04
An: firebird-support@yahoogroups.com
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