1

Im using wordpress for my website and i have a massive amount of data that i want to export through SQL. The thing is that I'm receiving for each post(item) several rows with data under the category column. What I'm trying to do is merge all the categories so eventually i will have only 1 row for each post with all the categories.

This is currently my query:

    SELECT post_title, b.object_id, b.term_taxonomy_id, c.term_id, d.name
FROM wp_posts a
JOIN wp_term_relationships b ON a.ID = b.object_id
JOIN wp_term_taxonomy c ON b.term_taxonomy_id = c.term_taxonomy_id
JOIN wp_terms d ON d.term_id = c.term_id

here is an example of my results:

post_title     object_id  term_taxonomy_id  term_id name
Holcroft Covenant   2       5                 5         Action
Holcroft Covenant   2       6                 6         Drama
Holcroft Covenant   2       8                 8         Thriller

The Result that i would like is: Holcroft Covenant 2 5 5 Action,Drama,Thriller

Any Idea's?

nimi
  • 917
  • 2
  • 14
  • 28
  • Take a look at this post: http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server – Marco Jun 21 '12 at 06:20
  • I gave a look at that post... The thing is that he gave a solution that fits the exact values that he has... Isn't there a more general solution that can fit my needs? – nimi Jun 21 '12 at 06:36
  • Actually, I've managed to do so with group_concat function...It seems to work great! – nimi Jun 21 '12 at 07:23
  • I thought you were working with Ms-Sql; if you're using mysql that's the way – Marco Jun 21 '12 at 07:37

1 Answers1

0

If you're using MySql try this:

SELECT 
    post_title, b.object_id, b.term_taxonomy_id, c.term_id, GROUP_CONCAT(d.name)
FROM wp_posts a
    JOIN wp_term_relationships b ON a.ID = b.object_id
    JOIN wp_term_taxonomy c ON b.term_taxonomy_id = c.term_taxonomy_id
    JOIN wp_terms d ON d.term_id = c.term_id
GROUP BY post_title, b.object_id, b.term_taxonomy_id, c.term_id
Marco
  • 56,740
  • 14
  • 129
  • 152