Wordpress SQL: get post category and tags

As noted in the comments, I was including an aggregate function, but no "group by" clause.

Now this seems to work (just added the GROUP BY line):

SELECT    p.id,    p.post_name,    c.name,    GROUP_CONCAT(t.`name`)FROM wp_posts pJOIN wp_term_relationships cr    on (p.`id`=cr.`object_id`)JOIN wp_term_taxonomy ct    on (ct.`term_taxonomy_id`=cr.`term_taxonomy_id`    and ct.`taxonomy`='category')JOIN wp_terms c on    (ct.`term_id`=c.`term_id`)JOIN wp_term_relationships tr    on (p.`id`=tr.`object_id`)JOIN wp_term_taxonomy tt    on (tt.`term_taxonomy_id`=tr.`term_taxonomy_id`    and tt.`taxonomy`='post_tag')JOIN wp_terms t    on (tt.`term_id`=t.`term_id`)GROUP BY p.id+---------------+----------+----------------+| post_id       | category | tags           ||---------------+----------+----------------+| 213           | news     | tag1,tag2,tag3 |+---------------+----------+----------------+| 216           | whatever | tag2,tag3      |+---------------+----------+----------------+

Thank you Strawberry!

This is based off of Pierre's code, expanded to concatenate multiple categories, as well as include posts with no categories or tags.

SELECT    p.id,    p.post_name,    GROUP_CONCAT(DISTINCT c.`name`) as categories,    GROUP_CONCAT(DISTINCT t.`name`) as tagsFROM wp_posts pLEFT JOIN wp_term_relationships cr    on (p.`id`=cr.`object_id`)LEFT JOIN wp_term_taxonomy ct    on (ct.`term_taxonomy_id`=cr.`term_taxonomy_id`    and ct.`taxonomy`='category')LEFT JOIN wp_terms c on    (ct.`term_id`=c.`term_id`)LEFT JOIN wp_term_relationships tr    on (p.`id`=tr.`object_id`)LEFT JOIN wp_term_taxonomy tt    on (tt.`term_taxonomy_id`=tr.`term_taxonomy_id`    and tt.`taxonomy`='post_tag')LEFT JOIN wp_terms t    on (tt.`term_id`=t.`term_id`)GROUP BY p.id