Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is locationName the primary key of merchant table (or a Unique key)? If not, the first query does not make sense. –  ypercube Jan 18 '13 at 18:06
    
It isn't. If I don't group by locationName there are duplicates. –  nick Jan 18 '13 at 18:41
    
Do you mean you want to see results from 5 merchants that are from different locations? –  ypercube Jan 18 '13 at 18:44
    
I want to see results from 5 different merchants in the same zip code. –  nick Jan 18 '13 at 18:45
    
What is the Primary Key of table merchant? –  ypercube Jan 18 '13 at 18:50
show 3 more comments

3 Answers

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)
;
share|improve this answer
    
'666666' is not constant, he is getting it from the result of the first query. –  ypercube Jan 18 '13 at 18:00
    
I know the query is not clean, I am still looking... –  RolandoMySQLDBA Jan 18 '13 at 18:01
    
I removed the constant, please try this... –  RolandoMySQLDBA Jan 18 '13 at 18:03
    
merchant.meta_value doesn't exist. we're close though. –  nick Jan 18 '13 at 18:10
    
Nope, 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 –  nick Jan 18 '13 at 18:13
show 35 more comments

Try this, it will show 5 merchants, with different locationName:

SELECT
    m.brandIdentifier, 
    m.locationIdentifier, 
    m.locationName,

    ( SELECT pm.post_id 
      FROM wp_postmeta AS pm
      WHERE pm.meta_key = 'locationIdentifier' 
        AND pm.meta_value = m.locationIdentifier
      LIMIT 1
    ) AS post_id,

    ( SELECT b.brandLogoName 
      FROM brand AS b
      WHERE b.brandIdentifier = m.brandIdentifier 
      LIMIT 1
    ) AS brandLogoName 
FROM
        merchant AS m
    JOIN
        ( SELECT DISTINCT 
              MIN(recordIdentifier) AS pk     -- the PRIMARY KEY of the table `merchant`
          FROM 
              merchant
          WHERE 
              postalCode = '90210'
          GROUP BY 
              locationName
          LIMIT 5 
        ) AS dm
            ON dm.pk = m.recordIdentifier 
;

This one will show just 5 merchants (perhaps with same locationName):

SELECT
    m.brandIdentifier, 
    m.locationIdentifier, 
    m.locationName,

    ( SELECT pm.post_id 
      FROM wp_postmeta AS pm
      WHERE pm.meta_key = 'locationIdentifier' 
        AND pm.meta_value = m.locationIdentifier
      LIMIT 1
    ) AS post_id,

    ( SELECT b.brandLogoName 
      FROM brand AS b
      WHERE b.brandIdentifier = m.brandIdentifier 
      LIMIT 1
    ) AS brandLogoName 
FROM
    merchant AS m
WHERE 
    postalCode = '90210'
LIMIT 5 ;
share|improve this answer
    
Takes 17sec to run, but appears to work. I'm getting some NULL post_id. I need to see if there actually are any NULL ones. –  nick Jan 18 '13 at 19:04
    
There are no NULLS in that table. Not sure where they are coming from. –  nick Jan 18 '13 at 19:06
    
If you have brandIdentifier in the merchant table without a matching value in the brand table, you'll get NULL in the brandLogoName column in the results, yes. The same applies to the post_id column. –  ypercube Jan 18 '13 at 19:20
add comment

Try this:

SELECT
    m.brandIdentifier,
    m.locationIdentifier,
    m.locationName,
    p.post_id,
    b.BrandLogoName
FROM (merchant AS m
    LEFT JOIN wp_postmeta AS p 
    ON p.meta_value = m.locationIdentifier )

    LEFT JOIN brand AS b 
    ON b.brandIdentifier = m.brandIdentifier 
    WHERE b.brandIdentifier = '11111111' AND p.meta_value = '666666'
    ;

http://www.sqlfiddle.com/#!2/bd53c/1

I have create a table structure to test the query, because you didn't supply one. In the future please supply a table structure with sample data.

CREATE TABLE merchant
    (`brandIdentifier` int primary key,
     `locationIdentifier` varchar(30),
     `locationName` varchar(30),
     `postalCode` int(5)
    );

CREATE TABLE wp_postmeta (
  post_id int auto_increment primary key,
  meta_key varchar(30),
  meta_value varchar (6)

);

CREATE TABLE brand (
  brandIdentifier int primary key,
  BrandLogoName varchar(30)
  );

I have inserted sample data.

INSERT INTO brand (brandIdentifier,BrandLogoName)
VALUES ('11111111',"brand.png"),
('55555555',"apple.png"),
('99999999',"microsoft.png"); 

INSERT INTO wp_postmeta (meta_key, meta_value)
VALUES 
("locationIdentifier", '666666'),
("locationIdentifier1",'999999'),
("locationIdentifier", '777777'),
("locationIdentifier", '888888'),
("locationIdentifier", '000000');

INSERT INTO merchant
    (`brandIdentifier`, `locationIdentifier`, `locationName`, `postalCode`)
VALUES
    (11111111, '666666', 'ABC',90210),
    (22222222, '777777', 'DEF',70210),
    (33333333, '888888', 'GHI',80210),
    (44444444, '999999', 'JKL',60210),
    (44444499, '999999', 'JKL',60210),
    (55555555, '000000', 'MNO',50210);

There where several problems one is the constant you have encountered. The constant is due to an join in which you combine two table on using different type of values. E.g. varchar with integer values. I would like to suggest that you take a look at the column types. Numbers should be integers preferably and not varchar.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.