범주 및 하위 범주 테이블을 사용한 쿼리

Question

내가하려는 PHP MySQL 쿼리가 있습니다. 카테고리 및 하위 카테고리가있는 항목 목록이 있습니다. 목록을 알파벳순으로 정렬하고 싶지만 목록에는 범주 (및 포함 된 항목)가 있습니다. 예를 들어 ...

세 개의 테이블이 있습니다 ... 카테고리 테이블 (itemcats) ...

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

하위 범주 테이블 (itemsubcats) 및 상위 범주의 열 (itemcat) ...

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

그리고 개별 항목 (항목)에 대한 표 ....

> 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

그리고 원하는 결과. 참고 : PHP를 사용하여 카테고리 / 하위 카테고리 이름을 추가 할 수 있지만 아래와 같이 주문한 개별 항목 목록이 필요합니다.

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

지금까지 몇 가지 JOINS를 시도했지만 원하는 결과를 얻지 못하는 것 같습니다 ... 여기에 제가 현재 가지고있는 것이 있습니다 ...

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

---

업데이트 : 여기에 잘 작동하는 것 같은 결승전이 있습니다 ....

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

order by 절을 수정하고 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

데모

트리보기의 경우 애플리케이션 레이어 (PHP)에서이 데이터를 변환 할 수 있습니다.