Query con tabelle Categoria e Sottocategoria

Aug 21 2020

Ho una query PHP MySQL che sto cercando di fare ... Ha un elenco di elementi con categorie e sottocategorie. Voglio ordinare l'elenco in ordine alfabetico, ma con le categorie (e gli elementi che le contengono) nell'elenco. Per esempio...

Ci sono tre tabelle... Una tabella delle categorie (itemcats)...

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

Una tabella di sottocategoria (itemsubcats) e una colonna della categoria principale (itemcat) ...

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

E poi la tabella per le singole voci (articoli)....

> 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

E i risultati desiderati. Nota: probabilmente posso aggiungere i nomi di categoria/sottocategoria utilizzando PHP, ma ho bisogno dell'elenco dei singoli elementi ordinati come di seguito ...

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

Finora ho provato alcuni JOIN... ma non riesco a ottenere i risultati desiderati... Ecco quello che ho attualmente...

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

AGGIORNAMENTO: Ecco il finale, che sembra funzionare alla grande....

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'

Risposte

MKhalidJunaid Aug 21 2020 at 03:21

Devi correggere il tuo ordine per clausola e utilizzare i nomi per la categoria e la sottocategoria, non i loro ID

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

DEMO

Per la visualizzazione ad albero puoi trasformare questi dati nel tuo livello di applicazione (PHP)