Autor Zpráva
ronnie
Profil
Dobrý den,
mám dotaz


SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS'


který vrací


+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+


Napadá vás, jak ho jednoduše upravit, aby vracel


+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | NULL | NULL | NULL |
| ELECTRONICS | TELEVISIONS | NULL | NULL |
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+


tedy i sloupce jako ELECTRONICS s hodnotami NULL v lev2, lev3, lev4 ap.?
Řešit to jde více dotazy, ale chtěl bych nějak upravit ten předchozí dotaz a večer už mi to nemyslí:)
ronnie
Profil
Napadlo mě jen takové řešení:



SELECT CONCAT_WS( ",", t2.name, t3.name, t4.name ) AS path, t4.id as id
FROM category AS t1, category AS t2, category AS t3, category AS t4
WHERE t2.parent = t1.id
AND t3.parent = t2.id
AND t4.parent = t3.id
AND t1.name = "eshop"
UNION
SELECT CONCAT_WS( ",", t2.name, t3.name ) AS path, t3.id as id
FROM category AS t1, category AS t2, category AS t3
WHERE t2.parent = t1.id
AND t3.parent = t2.id
AND t1.name = "eshop"
UNION
SELECT CONCAT_WS( ",", t2.name) AS path, t2.id as id
FROM category AS t1, category AS t2
WHERE t2.parent = t1.id
AND t1.name = "eshop"


Tohle ale bude pomalé:/
Toto téma je uzamčeno. Odpověď nelze zaslat.