Query to retrieve sorted data from several tables - not sure where to start

[cranjled]

I have 3 tables in a mysql database: categories, lists, and items with the following fields:

Categories: id, label
Lists: id, cat_id, label, weight
Items: id, list_id, label, weight, status

I would like to retrieve results from the database so that items will be returned in the following structure, so that the items will be stored in arrays under their respective lists, and lists will be stored in arrays under their respective categories, such as:

Category 1
----- List 1
---------- Item 1
---------- Item 2
---------- Item 3
----- List 2
---------- Item 1
---------- Item 2
---------- Item 3

Category 2
----- List 1
---------- Item 1
---------- Item 2
---------- Item 3
----- List 2
---------- Item 1
---------- Item 2
---------- Item 3

I believe this can be done with a single query - could someone please post a query that would achieve this?

Thank you!

[r937]

here ya go…

SELECT c.label AS category , l.label AS list , l.weight AS listweight , i.label AS item , i.weight AS itemweight , i.status FROM categories AS c INNER JOIN lists AS l ON l.cat_id = c.id INNER JOIN items AS i ON i.list_id = l.id ORDER BY c.label , l.label , i.label
to suppress second and subsequent labels, you would do current/previous checking in your application language (php or whatever)