I have created a custom post type and taxonomy for a goods catalogue for a hire website. This works fine. I am trying to generate a report that lists all items and their category and sub category. I came up with this to generate a table with the main category:

SELECT 
wp_posts.ID as ID, 
wp_posts.post_title as post_title,
wp_terms.name as main_category,
wp_terms.term_id as main_category_id
FROM 
wp_posts, wp_term_relationships, wp_terms, wp_term_taxonomy
WHERE
wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
AND wp_term_taxonomy.term_id = wp_terms.term_id
AND wp_term_taxonomy.parent = '0'
AND wp_posts.post_type = 'goods' 
AND wp_posts.post_status = 'publish'
ORDER BY wp_terms.term_id ASC

I just loop through and its fine. But I want to show the sub category too – which I can do by putting another query in the foreach php loop.

BUT, I need to order by main category and then sub category.

So I figured I need it all in one query. And it must be possible. I have tried experimenting with JOINing tables in the query but can’t get the syntax right. Heres the latest one I tried.

SELECT 
wp_posts.ID as ID, 
wp_posts.post_title as post_title,
s1.name as main_category,
s1.term_id as main_category_id,
s2.name as main_category,
s2.term_id as main_category_id
FROM 
wp_posts, wp_term_relationships, wp_terms s1, wp_term_taxonomy
LEFT JOIN wp_terms s2
ON s1.term_id = s2.id
WHERE
wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
AND wp_term_taxonomy.term_id = s1.term_id
AND wp_term_taxonomy.parent = '0'
AND wp_posts.post_type = 'goods' 
AND wp_posts.post_status = 'publish'
ORDER BY s1.term_id ASC