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

Tags: Mysql / Wordpress

Similar questions

use mysql to replace one custom field value with another custom field value
I have two custom fields, and both have numeric values: tnid_01 tnid_01old Custom Field 'tnid_01' exists for all posts. Custom Field 'tnid_01old' only exists for some posts. I am trying to replace the value of 'tnid_01' with the value of 'tnid_01old', but only if 'tnid_01old' exists. This is what i have so far, but i get a mysql error: thx
If Custom Field Value exists display, if not display another Custom field value
I have a Wordpress managed web site at http://www.urbanvision.org.uk/ I have built it and everything works like I want it to and I'm happy with the outcome as it is my first fully built Wordpress web site. I've become stuck on a request I had at the start of the week. We have a Properties for Sale page (http://www.urbanvision.org.uk/services/proper...
Get all posts that have a custom field value with meta key and loop the query results from another field value
how can i get all posts of both custom post type 'folder' and 'file' that have the same custom field value in custom field 'wpcf-secret-id-1' and in case if there are results i would like to show the value of another custom field 'wpcf-secret-id-2'. I have tried following code but seems not to work: function get_all_post_from_field_valu...
Separate user bases or hide users of another role or connected to another minisite
I am creating a WordPress site in which I want to create mini-sites/sub-sites meaning each of them has their own separate user base. Alternatively, it's ok that all the users are in the same base, but that users that are not in the same role, group or those connected to a subsite are hidden. This could be done either with a plugin or that I create ...
Show subtotal excl. tax, add subtotal tax as separate row on Woocommerce checkout
Currently I have all my products set to include tax. Because of rules in my country I would like to have my subtotal without tax, then a line with the amount of tax being paid and then the total with all the taxes (which is already default) In my review-order.php the line <td><?php wc_cart_totals_subtotal_html(); ?></td> gets call...
main menu in one row then sub menu in another horizontal row in wordpress
i am using word press. actually i want menu plugin for main menu menu in one row then the sub menus in another row. any one know which menu plugin is used for that scenario? Menu:

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.