Consulta con tablas de categorías y subcategorías

Question

Tengo una consulta PHP MySQL que estoy tratando de hacer... Tiene una lista de elementos con categorías y subcategorías. Quiero ordenar la lista alfabéticamente, pero con categorías (y los elementos que las contienen) en la lista. Por ejemplo...

Hay tres tablas... Una tabla de categorías (itemcats)...

> itemcat   catname 
> 1         AGeneralCategory
> 2         Food
> 3         Clothing

Una tabla de subcategorías (itemsubcats) y una columna de la categoría principal (itemcat)...

> itemsubcat     itemcat   subcatname 
> 1              2         Fruit
> 2              2         Vegetables
> 3              2         Meat         
> 4              3         Shoewear
> 5              3         Hats

Y luego la tabla para los artículos individuales (artículos)....

> itemid   itemname       itemcat   itemsubcat    
> 1        Apples         2         1
> 2        Sneakers       3         4
> 3        Onion          2         2
> 4        Banana         2         1
> 5        Steak          2         3
> 6        Sombrero       3         5
> 7        Sandals        3         4
> 8        Jeep           1         null
> 9        Baseball Cap   3         5
> 10       Mountain       1         null
> 11       Bread          2         null
> 12       Veggiemite     2         null

Y los resultados deseados. Nota: probablemente pueda agregar los nombres de categoría/subcategoría usando PHP, pero necesito la lista de elementos individuales ordenados como se muestra a continuación...

> [AGeneralCategory]
>   Jeep
>   Mountain
> [Clothing]
>   [Headwear]
>     Baseball Cap
>     Sombrero
>   [Shoewear]
>     Sandals
>     Sneakers
> [Food]
>   Bread
>   [Fruit]
>     Apple
>     Banana
>   [Meat]
>     Steak
>   Veggiemite
>   [Vegetables]
>     Onion

Hasta ahora he probado algunas UNIONES... pero parece que no puedo obtener los resultados deseados... Esto es lo que tengo actualmente...

SELECT a1.itemid iid, a1.itemname iname, a1.itemcat icat, a1.itemsubcat isub 
  FROM items a1 
  LEFT 
  JOIN itemcats b1 
    ON a1.itemcat = b1.itemcat 
  LEFT 
  JOIN itemsubcats c1 
    ON a1.itemsubcat = c1.itemsubcat 
 ORDER 
    BY a1.itemcat, a1.itemsubcat

---

ACTUALIZACIÓN: Aquí está la versión final, que parece funcionar muy bien....

SELECT a.itemid,
        a.itemname, 
        a.itemcost,
        a.itemcostcoin,
        a.itemweight,
        a.itemdesc,
        a.itemcat,
        b.catname,  
        a.itemsubcat,
        c.subcatname
    FROM (SELECT
        itemid,
        itemcat,
        itemsubcat,
        itemname, 
        itemcost,
        itemcostcoin,
        itemweight,
        itemdesc
      FROM items
      UNION SELECT itemsubcat, 
        NULL AS itemcat,
        NULL AS itemsubcat,
        subcatname,
        NULL AS itemcost,
        NULL AS itemcostcoin,
        NULL AS itemweight,
        NULL AS itemdesc        
      FROM itemsubcats) 
    AS a 
LEFT JOIN itemcats b ON a.itemcat=b.itemcat 
LEFT JOIN itemsubcats c ON a.itemsubcat=c.itemsubcat 
WHERE a.itemcat='1'

Answer 1

Debe corregir su orden por cláusula y usar nombres para categoría y subcategoría, no sus identificaciones

SELECT 
  b1.catname as catname,
  c1.subcatname as subcatname ,
  a1.itemid as iid,
  a1.itemname as iname
FROM items a1 
LEFT JOIN itemcats b1 ON a1.itemcat=b1.itemcat 
LEFT JOIN itemsubcats c1 ON a1.itemsubcat=c1.itemsubcat 
ORDER BY b1.catname, c1.subcatname

MANIFESTACIÓN

Para la vista de árbol, puede transformar estos datos en su capa de aplicación (PHP)