kil lie kil lie - 3 months ago 7
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.

SELECT
`wp_posts`.`ID`,
`wp_posts`.`post_content`,
`wp_posts`.`post_title`
FROM(
`wp_posts`
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

Thanks

Answer

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

SELECT 
 `wp_posts`.`ID`,
`wp_posts`.`post_content`,
`wp_posts`.`post_title`,
`wp_postmeta`.meta_key,
`wp_postmeta`.meta_value
FROM
`wp_posts`
LEFT JOIN `wp_postmeta` ON `wp_posts`.`ID` = `wp_postmeta`.`post_id`
                        AND `wp_postmeta`.meta_key in ('responsible_officer', 'responsible_service')