Sql query to get posts, categories and tags related to them?

Question

MySQL

Hello, I need to transfer posts from a site on WP version 4.9.8 to another one with a different database structure. To do this, you need to get all articles with categories and tags.
Please let me know how to get posts, thank you.

Answer 1

Here are different SQL queries... adapt them to your needs:

SELECT wp_posts.ID, wp_posts.post_title, wp_terms.name, wp_postmeta.meta_key, wp_postmeta.meta_value
FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type='post';

SELECT *
FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type='post';

Answer 2

Posts are stored in the **_posts table, with the condition that post_type=post.
** - instead, use your prefix, which was specified during installation.
So you can pull all posts with conditions:
SELECT * FROM `wp_posts` WHERE post_type=`post` AND post_status=`publish` WHERE id IN ***)