Get post title, custom meta value and assigned category name using mysql query?
SELECT wp_posts.ID, wp_posts.post_title, wp_terms.name as category, myvideo.meta_value AS video_id, coutryval.meta_value AS country
FROM wp_posts
LEFT JOIN wp_postmeta as coutryval ON wp_posts.ID = coutryval.post_id
LEFT JOIN wp_postmeta as myvideo ON wp_posts.ID = myvideo.post_id
LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
WHERE 1
AND myvideo.meta_key = '_client_comp_vid_id'
AND coutryval.meta_key = '_country'
AND wp_posts.post_type = 'client_competitions' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC limit 0, 700
Get post title, custom meta value,assigned category name and featured image path using mysql query?
SELECT wp_posts.ID, wp_posts.post_title, wp_terms.name as category, myvideo.meta_value AS video_id, coutryval.meta_value AS country, img.meta_value AS image_id , imgtable.guid as image_path
FROM wp_posts
LEFT JOIN wp_postmeta as coutryval ON wp_posts.ID = coutryval.post_id
LEFT JOIN wp_postmeta as myvideo ON wp_posts.ID = myvideo.post_id
LEFT JOIN wp_postmeta as img ON wp_posts.ID = img.post_id
LEFT JOIN wp_posts as imgtable ON wp_posts.ID = imgtable.post_parent
LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
WHERE 1
AND myvideo.meta_key = '_client_comp_vid_id'
AND coutryval.meta_key = '_country'
AND img.meta_key = '_thumbnail_id'
AND wp_posts.post_type = 'client_competitions' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
