sql - MySQL join显示类别、子类别和子子类别

Question

我有三个表:类别、子类别和子子类别。

我想显示一个格式如下的列表:

dvdscdscds > popcds > bluescds > new agecds > new age > whale noisesbooksbooks > cowboysbooks > zombies

I've managed to display everything except for the names of categories on their own when they have children, eg what I'm getting is:

dvdscds > popcds > bluescds > new age > whale noisesbooks > cowboysbooks > zombies

The above list is missing the cds and books categories, plus the cds > new age subcategory.

The query I'm using is:

SELECT
  c.name AS c_name,
  sc.name AS sc_name,
  ssc.name AS ssc_name
FROM
  categories c
LEFT JOIN
  subcategories sc
  ON c.id = sc.category_id
LEFT JOIN
  subsubcategories ssc
  ON sc.id = ssc.subcategory_id

如有任何帮助，我们将不胜感激!

Answer 1

SELECT  *
FROM    (
        SELECT  DISTINCT
                c.name AS c_name,
                sc.name AS sc_name,
                ssc.name AS ssc_name
        FROM    categories c
        LEFT JOIN
                subcategories sc
        ON      c.id = sc.category_id
        LEFT JOIN
                subsubcategories ssc
        ON      sc.id = ssc.subcategory_id
        GROUP BY
                c.name, sc.name, ssc.name WITH ROLLUP
        HAVING  c_name IS NOT NULL
        ) q
ORDER BY
        c_name, sc_name, ssc_name