kil lie kil lie -4 years ago 175
SQL Question

MySQL multple selects on second table

I am trying to construct a select statement. The table structure is shown below. I am basically looking to select the three fields on the left table along with the meta_values of the right hand side table that have a meta_key of responsible_service and responsible_officer. These meta_values may or may not exist.

My awful attempt looks like this. Any help would be great.

INNER JOIN `wp_postmeta` ON (`wp_posts`.`ID` = `wp_postmeta`.`post_id`),
(Select `wp_postmeta`.meta_value where`wp_postmeta`.meta_key='responsible_officer') as Responsible Officer),
(Select `wp_postmeta`.meta_value where `wp_postmeta`.meta_key='responsible_service') as Responsible Service ),

enter image description here


Answer Source

This will give you 1 or more rows per wp_posts.ID depending on how many 'responsible_officer' and 'responsible_service' are found. If none are found meta_key and meta_value will be null

LEFT JOIN `wp_postmeta` ON `wp_posts`.`ID` = `wp_postmeta`.`post_id`
                        AND `wp_postmeta`.meta_key in ('responsible_officer', 'responsible_service')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download