MySQL joins to display categories, subcategories and subsubcategories

Question

I have three tables: categories, subcategories and subsubcategories.

I want to display a list that's formatted like:

dvds
cds
cds > pop
cds > blues
cds > new age
cds > new age > whale noises
books
books > cowboys
books > 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:

dvds
cds > pop
cds > blues
cds > new age > whale noises
books > cowboys
books > 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

Any help with this would be much appreciated!

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