title: Wordpress display taxomony terms and sub terms MYSQL PHP
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>BUT, I need to order by main category and then sub category. </p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>