How do I show a row based on the value of another field in a separate row?
In the table below, the meta keys
logo_ID
and
show_logo
are associated with each other based on the
post_id
field. How do I get the value of
logo_ID
for rows which have a
show_logo
of
1
using
only mysql
?
post_id meta_key meta_value
------- ----------- ----------
262 logo_ID 263
262 show_logo 0
260 logo_ID 261
260 show_logo 1
258 logo_ID 259
258 show_logo 1
If it looks familiar it's because this is from the WP
postmeta
table. I'm using
$wpdb
to query the db so everything has to be in mysql (no WP functions anywhere).
Solutions
pivot the rows by using
CASE
statement based
on the values of
meta_key
so you can show the values of
post_id
in a row, then wrap it in a subquery.
SELECT *
FROM
(
SELECT post_id,
MAX(CASE WHEN meta_key = 'logo_ID' THEN meta_value ELSE NULL END) logo_ID,
MAX(CASE WHEN meta_key = 'show_logo' THEN meta_value ELSE NULL END) show_logo
FROM tableName
GROUP BY post_ID
) a
WHERE show_logo = 1
- SQLFiddle Demo
You can join the
wp_postmeta
table to itself, specifying the value you want in the
WHERE
clause.
SELECT pm2.post_id, pm2.meta_key
FROM wp_postmeta pm1
JOIN wp_postmeta pm2
ON pm2.post_id = pm1.post_id AND pm2.meta_key = 'logo_ID'
WHERE pm1.meta_key = 'show_logo' AND pm1.meta_value = 1
If you are dealing with a lot of data, this is fairly fast as it will use indexes on the
wp_postmeta
table. You can
JOIN
the
wp_postmeta
more if you want to get other values as criteria as well, or the
wp_posts
table to get the other
post fields
.
SQL Fiddle