CODEWITHSUNDEEP
mysqlwordpress
enchance
enchance

Reputation: 30421

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).

Upvotes: 0

Views: 57

Answers (2)

doublesharp
doublesharp

Reputation: 27609

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

Upvotes: 2

John Woo
John Woo

Reputation: 263723

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

Upvotes: 1

Related Questions