MySQL query to create columns based on other tables

I am trying to query multiple WordPress tables and I’ve been learning as I go.

Here’s what I have so far

Read More
SELECT 
   tr.object_id, 
   tr.term_taxonomy_id, 
   p.ID, 
   p.post_date, 
   p.post_title, 
   p.post_excerpt, 
   p.guid, 
   t.term_id, 
   t.name, 
   tt.taxonomy
FROM 
   wp_116_term_relationships AS tr, 
   wp_116_posts AS p, 
   wp_116_terms AS t LEFT JOIN 
   wp_116_term_taxonomy as tt ON tt.term_id = t.term_id
WHERE 
   p.post_type = 'post'
   AND p.ID = tr.object_ID
   AND tr.term_taxonomy_id = tt.term_taxonomy_id
   AND p.post_date > '2013-06-01'

Here’s what I get (sorry I couldn’t figure out how to post this cleaner – hope it makes sense)

object_id term_taxonomy_id ID post_date post_title post_excerpt guid term_id name  taxonomy 
2356     33      2356    2013-06-07 15:56:54     Test Post for Reports       http://domain.com/?p=2356   7496    Marketing Updates   category
2356     32      2356    2013-06-07 15:56:54     Test Post for Reports       http://domain.com/?p=2356   470     News Updates    category 
2356     70      2356    2013-06-07 15:56:54     Test Post for Reports       http://domain.com/?p=2356   46408   Tag Test 1      post_tag 
2356     72      2356    2013-06-07 15:56:54     Test Post for Reports       http://domain.com/?p=2356   46410   Tag Test 2      post_tag 
2356     74      2356    2013-06-07 15:56:54     Test Post for Reports       http://domain.com/?p=2356   46412   Tag Test 3      post_t

How do I separate the data from the name field so that if it’s a post_tag it’s in one column (post_tag) and if it’s a category it’s in another (category)? For example:

object_id term_taxonomy_id ID post_date post_title post_excerpt guid term_id post_tag category
2356     33      2356    2013-06-07 15:56:54     Test Post for Reports       http://domain.com/?p=2356   7496    Marketing Updates   Tag Test 1

Related posts

Leave a Reply

1 comment

  1. Should be fairly straight forward with a CASE statement

       SELECT 
        tr.object_id, 
        tr.term_taxonomy_id, 
        p.ID, 
        p.post_date, 
        p.post_title, 
        p.post_excerpt, 
        p.guid, 
        t.term_id, 
        CASE WHEN tt.taxonomy = 'category' THEN t.name ELSE NULL END AS category_name,
        CASE WHEN tt.taxonomy = 'post_tag' THEN t.name ELSE NULL END AS post_tag_name
        FROM 
        wp_116_term_relationships AS tr, 
        wp_116_posts AS p, 
        wp_116_terms AS t
        LEFT JOIN wp_116_term_taxonomy as tt ON tt.term_id = t.term_id
        WHERE 
        p.post_type = 'post'
        AND p.ID = tr.object_ID
        AND tr.term_taxonomy_id = tt.term_taxonomy_id
        AND p.post_date > '2013-06-01'