Ja bym zamiast trawersować, przeszedł na metodę ścieżek wtedy latwiej odpytać o konkretne kategorie, czyli po kolei:
Stworzyłem widok cat_path:
create view cat_path
as
select 0 lvl, t0.id_cat,concat_ws('/',t0.id_cat) node_path,t0.name from
cat t0
where
t0.id_parent=0
union all
select 1 lvl, t2.id_cat,concat_ws('/',t1.id_cat,t2.id_cat),t2.name from
cat t1
inner join cat t2 on t2.id_parent = t1.id_cat
where
t1.id_parent=0
union all
select 2 lvl, t3.id_cat,concat_ws('/',t1.id_cat,t2.id_cat,t3.id_cat),t3.name from
cat t1
inner join cat t2 on t2.id_parent = t1.id_cat
inner join cat t3 on t3.id_parent = t2.id_cat
where
t1.id_parent=0
union all
select 3 lvl, t4.id_cat,concat_ws('/',t1.id_cat,t2.id_cat,t3.id_cat,t4.id_cat),t4.name from
cat t1
inner join cat t2 on t2.id_parent = t1.id_cat
inner join cat t3 on t3.id_parent = t2.id_cat
inner join cat t4 on t4.id_parent = t3.id_cat
where
t1.id_parent=0
który zwraca takie dane
lvl |
id_cat |
node_path |
name |
0 |
1 |
1 |
TYP |
0 |
6 |
6 |
PRODUCENT |
0 |
33 |
33 |
KOLOR |
1 |
2 |
1/2 |
Sedan |
1 |
3 |
1/3 |
Kombi |
1 |
4 |
1/4 |
SUV |
1 |
5 |
1/5 |
Hatchback |
1 |
7 |
6/7 |
Audi |
1 |
16 |
6/16 |
BMW |
1 |
24 |
6/24 |
VW |
1 |
34 |
33/34 |
Mat |
1 |
37 |
33/37 |
Metalik |
1 |
41 |
33/41 |
Perłowy |
2 |
8 |
6/7/8 |
A3 |
2 |
9 |
6/7/9 |
A4 |
2 |
13 |
6/7/13 |
A6 |
2 |
17 |
6/16/17 |
Seria 1 |
2 |
20 |
6/16/20 |
Seria X |
2 |
25 |
6/24/25 |
Passat |
2 |
29 |
6/24/29 |
Golf |
2 |
35 |
33/34/35 |
Czarny |
2 |
36 |
33/34/36 |
Biały |
2 |
38 |
33/37/38 |
Czerwony |
2 |
39 |
33/37/39 |
Srebrny |
2 |
40 |
33/37/40 |
Zielony |
2 |
42 |
33/41/42 |
Biały |
2 |
43 |
33/41/43 |
Niebieski |
3 |
10 |
6/7/9/10 |
B5 |
3 |
11 |
6/7/9/11 |
B6 |
3 |
12 |
6/7/9/12 |
B7 |
3 |
14 |
6/7/13/14 |
C6 |
3 |
15 |
6/7/13/15 |
C7 |
3 |
18 |
6/16/17/18 |
E87 |
3 |
19 |
6/16/17/19 |
E81 |
3 |
21 |
6/16/20/21 |
X3 |
3 |
22 |
6/16/20/22 |
X5 |
3 |
23 |
6/16/20/23 |
X6 |
3 |
26 |
6/24/25/26 |
B5 |
3 |
27 |
6/24/25/27 |
B6 |
3 |
28 |
6/24/25/28 |
B7 |
3 |
30 |
6/24/29/30 |
I |
3 |
31 |
6/24/29/31 |
II |
3 |
32 |
6/24/29/32 |
III |
Ważna jest kolumna node_path, bo to ona powinna się znaleźć w value option, dla przykładu dane dla producenta:
select node_path value_option,concat(repeat('-',(lvl-1)*2),name) option_innerhtml from cat_path
where node_path like '6/%'
order by 1
Wynik
value_option |
option_name |
6/16 |
BMW |
6/16/17 |
--Seria 1 |
6/16/17/18 |
----E87 |
6/16/17/19 |
----E81 |
6/16/20 |
--Seria X |
6/16/20/21 |
----X3 |
6/16/20/22 |
----X5 |
6/16/20/23 |
----X6 |
6/24 |
VW |
6/24/25 |
--Passat |
6/24/25/26 |
----B5 |
6/24/25/27 |
----B6 |
6/24/25/28 |
----B7 |
6/24/29 |
--Golf |
6/24/29/30 |
----I |
6/24/29/31 |
----II |
6/24/29/32 |
----III |
6/7 |
Audi |
6/7/13 |
--A6 |
6/7/13/14 |
----C6 |
6/7/13/15 |
----C7 |
6/7/8 |
--A3 |
6/7/9 |
--A4 |
6/7/9/10 |
----B5 |
6/7/9/11 |
----B6 |
6/7/9/12 |
----B7 |
Czyli wracając do przykładu mam wybranego
- Producenta: Audi: 6/7
- Kolor Perłowy: 33/41
Dodałem widok cat_prod_path tylko po to by dodać kolumnę node path do tabeli cat_prod.
I teraz jedynie co potrzebujemy to wykorzystać zapytanie, już podane wcześniej, ale wykorzystując kolumnę node_path:
SELECT
*
FROM
prod
WHERE
id_prod IN (
SELECT DISTINCT
kolor.id_prod
FROM (SELECT
id_prod
FROM
cat_prod
WHERE
id_cat IN (SELECT
id_cat
FROM
cat_prod_path
WHERE node_path like'33/41%')
) kolor
INNER JOIN (SELECT
id_prod
FROM
cat_prod
WHERE
id_cat IN (SELECT
id_cat
FROM
cat_prod_path
WHERE node_path like'6/7%')
) producent ON producent.id_prod = kolor.id_prod
)
Przeanalizuj: http://sqlfiddle.com/#!9/13a594/3