Mysql – How to best combine 3 queries into 1

MySQL

I need the following queries combined into one query that returns the following from all results from the first query you see below (Right now I load the first query into an array in php then run two queries against each result):

  • post_id from wp_postmeta
  • brandLogoName from brand

Here are the tables and their relevant columns:

  1. merchant – brandIdentifier, locationIdentifier, locationName, postalCode
  2. wp_postmeta – post_id, meta_key, meta_value
  3. brand – brandIdentifier, brandLogoName

Currently I run the following and load it into an array:

SELECT `brandIdentifier`,`locationIdentifier`,`locationName` FROM merchant WHERE postalCode = '90210';
GROUP BY `locationName` LIMIT 0, 5

Which returns:

brandIdentifier  locationIdentifier locationName
11111111         666666             ABC
22222222         777777             DEF
33333333         888888             GHI
44444444         999999             JKL
55555555         000000             MNO

Then for each line in the array I run the following (using the first result as an example):

SELECT post_id FROM wp_postmeta WHERE meta_key = 'locationIdentifier' AND meta_value = '666666' LIMIT 1;

Which returns:

post_id
123456

Then for each line in the array I run the following (using the first result as an example):

SELECT brandLogoName FROM brand WHERE brandIdentifier = '11111111' LIMIT 1;

Which returns:

brandLogoName
file.png

I know joins are required. I just don't know how to join to a result set. Can anyone point me in the right direction on writing this query? Thanks.

Best Answer

SUBSET QUERY #1

SELECT
    merchant.locationName,
    wp_postmeta_location.meta_value,
    wp_postmeta_location.post_id,
    merchant.brandIdentifier,
    wp_postmeta_brand.meta_value
FROM
    (
        SELECT `brandIdentifier`,`locationIdentifier`,`locationName`
        FROM merchant WHERE postalCode='90210'
        GROUP BY `locationName`,`locationIdentifier`
    ) merchant
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'locationIdentifier'
    ) wp_postmeta_location
    ON merchant.locationIdentifier = wp_postmeta_location.meta_value
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'brandIdentifier'
    ) wp_postmeta_brand
    ON
    (merchant.brandIdentifier = wp_postmeta_brand.meta_value AND
    wp_postmeta_location.post_id = wp_postmeta_brand.post_id)
;
Related Question