Queries

sql-queries.sql

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

SELECT *
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_ID
WHERE wp.post_type = 'review'

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_ID
WHERE wp.post_type = 'review'

SELECT * 
FROM wp_posts
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE wp_postmeta.meta_value  = 'yourvalue'

SELECT count(DISTINCT posts.ID) as count, AVG(odds.meta_value) as ave
FROM $wpdb->posts posts
LEFT JOIN $wpdb->postmeta stat
    ON posts.ID = stat.post_id
    AND stat.meta_key = 'status'
LEFT JOIN $wpdb->postmeta odds
    ON posts.ID = odds.post_id
    AND odds.meta_key = 'odds'
LEFT JOIN $wpdb->term_relationships tr
    ON posts.ID = tr.object_id
LEFT JOIN $wpdb->term_taxonomy t
    ON tr.term_taxonomy_id = t.term_taxonomy_id 
WHERE (stat.meta_value    = 'wrong'
   OR stat.meta_value    = 'correct')
   AND posts.post_status      = 'publish'
   AND t.taxonomy = 'category'
   AND (t.term_id  = 4
   OR t.term_id  = 5
   OR t.term_id  = 6)

   SELECT count(DISTINCT ID) as count
   FROM `wp_posts` 
   LEFT JOIN `wp_postmeta`
   ON wp_postmeta.meta_key = '74112'
   AND wp_postmeta.meta_value '74112'


   SELECT *
    FROM categories
    LEFT JOIN user_category_subscriptions 
         ON user_category_subscriptions.category_id = categories.category_id 
   WHERE user_category_subscriptions.user_id = 1 
          or user_category_subscriptions.user_id is null



SELECT count(DISTINCT wp_postmeta.`post_id`) 
FROM wp_posts
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE wp_postmeta.meta_key  = 'review_form_company_zip'
AND wp_postmeta.meta_value  = '74112'