CODEWITHSUNDEEP
sql
wazzaday
wazzaday

Reputation: 9664

SQL for the wordpress database

I need some help with an SQL query, im a bit rusty with this. The Wordpress database im using is set up like this.

post_id, meta_key, meta_value
33, opt_in, 1
33, email, [email protected]

how can i collect all email address' where the opt in has the value of '1', the post_id is what matches the two rows. I have looked at sql union, and this the closest I have got.

SELECT post_id
FROM wp_postmeta
WHERE meta_key='opt_in' AND meta_value='1'
UNION
SELECT meta_value
FROM wp_postmeta
WHERE meta_key='email'

Upvotes: 2

Views: 889

Answers (3)

peterm
peterm

Reputation: 92805

You can do it with JOIN

SELECT p2.meta_value email
  FROM wp_postmeta p1 JOIN wp_postmeta p2
    ON p1.post_id = p2.post_id
   AND p1.meta_key = 'opt_in'
   AND p2.meta_key = 'email'
 WHERE p1.meta_value = 1

If you were to have 

| POST_ID | META_KEY |        META_VALUE |
|---------|----------|-------------------|
|      33 |   opt_in |                 1 |
|      33 |    email |  [email protected] |
|      34 |   opt_in |                 0 |
|      34 |    email | [email protected] |
|      35 |   opt_in |                 1 |
|      35 |    email |  [email protected] |

Query output would be:

|            EMAIL |
|------------------|
| [email protected] |
| [email protected] |

Here is SQLFiddle demo

Upvotes: 2

fancyPants
fancyPants

Reputation: 51908

select meta_value 
from wp_postmeta w1
where meta_key = 'email'
and exists (select 1 from wp_postmeta w2 where w1.post_id = w2.post_id and w2.meta_key = 'opt_in' and meta_value = 1)

or

select w1.meta_value 
from wp_postmeta w1
inner join wp_postmeta w2 on w1.post_id = w2.post_id
and w1.meta_key = 'email' 
and w2.meta_key = 'opt_in' and w2.meta_value = 1

Upvotes: 1

No Idea For Name
No Idea For Name

Reputation: 11597

i think what you need is the meta_value field, not post_id 

SELECT meta_value 
FROM wp_postmeta
WHERE meta_key = 'email' AND
      post_id = (SELECT post_id 
                 FROM wp_postmeta 
                 WHERE meta_key = 'opt_in' AND
                       meta_value = 1)

Upvotes: 1

Related Questions