SQL query to extract all WordPress posts with categories

I need to extract all posts from my WordPress DB along with the associated categories and not sure how to write this query. I’ve taken a couple of stabs at it already with no joy and would appreciate the help?

EDIT: Here’s what I have tried already:

SELECT post_title, wpr.object_id, wp_terms.name
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr ON wpr.term_taxonomy_id = 
wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_posts ON ID = wpr.object_id
WHERE taxonomy = 'category' 
AND post_type = 'post' 
ORDER by post_title

This seems to work but it returns 1,553 where I know I only have 1343 in my DB.

EDIT:
We did the same thing on another SQL query a little while ago and found that it was pulling in the revisions and other post types but thought that this was resolved using post_type = ‘post’

EDIT:
Upon looking at the number of categories in the DB, I come up with a total number of 216, 6 off the number if you subtract 1553 – 1343 = 216. So I think this total number of 1553 is coming from the wp_terms table which needs to be excluded and only those that are active with published posts should be shown?

EDIT:
The other possibility is that each post can have multiple categories, hence the reason for having more posts (1553). So how could I separate each posts into multiple categories?

Many thanks!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This is the final answer that worked for me.

SELECT DISTINCT
post_title
, post_content
,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Asking Price (US$)' AND wp_postmeta.post_id = wp_posts.ID) AS "Asking Price (US$)"
,(SELECT group_concat(wp_terms.name separator ', ') 
    FROM wp_terms
    INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'category' and wp_posts.ID = wpr.object_id
) AS "Categories"
,(SELECT group_concat(wp_terms.name separator ', ') 
    FROM wp_terms
    INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'post_tag' and wp_posts.ID = wpr.object_id
) AS "Tags"
FROM wp_posts
WHERE post_type = 'post' 
ORDER BY
post_title
, post_content

Method 2

/* Query for fetch post/posts using post user, post category and post_title */

$query ="SELECT wp_posts.post_title, wp_posts.post_content, wp_posts.comment_count, wp_users.display_name, wp_terms.name  
                                        FROM wp_posts
                                        JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
                                        JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
                                        LEFT JOIN wp_terms ON (wp_terms.term_id = wp_term_taxonomy.term_id)
                                        JOIN wp_users ON (wp_posts.post_author = wp_users.ID)
                                        WHERE wp_term_taxonomy.term_id IN ($bycat)
                                        AND wp_users.ID = $byuser
                                        AND wp_posts.post_type = 'post'
                                        AND (wp_posts.post_content LIKE '$bytitle' OR wp_posts.post_title LIKE '$bytitle')
                                        AND wp_posts.post_status = 'publish' 
                                        ORDER BY wp_posts.post_modified DESC";


/*---- FOR DISPLAY RESULT -----*/
$resultfirst = $wpdb->get_results($query);
        
foreach( $resultfirst as $result ){
echo $result->post_title .'
';
echo $result->display_name.'
';
echo $result->name.'
';
echo $result->comment_count.'
';
} 

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0