Selecting minimum value using a subquery

Question

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating.

We have a table for prices (tgmp_prices), a table for merchants (tgmp_merchants) and a table for affiliate product ids (tgmp_affiliates)

I have already got the query to grab all of the prices and data that I need:

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code != '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p_type = 'games' 
    AND m.m_hide = 0 
ORDER BY p.p_price ASC

I just need to group the prices by merchant id (p.p_m_id or m.m_id) and select the lowest price for each merchant.

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code != '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p_type = 'games' 
    AND m.m_hide = 0 
GROUP BY m.m_id
ORDER BY p.p_price ASC

If I add GROUP BY m.m_id before ORDER BY I get 16 results, 1 for each merchant. I just need to make this the lowest price. So I tried adding a sub query where the prices table (tgmp_prices) is joined as so:

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code != '' 
        AND p.p_price = ( 
            SELECT MIN(p.p_price) 
            FROM tgmp_prices 
            WHERE p.p_m_id = m.m_id
        ) 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p_type = 'games' 
    AND m.m_hide = 0 
GROUP BY m.m_id
ORDER BY p.p_price ASC

This doesn't give me the correct results - I think the issue could possibly be that m.m_id isn't available when the sub query is run, so I've tried joining the merchants table within the sub query but I still end up with the same set of results.

DDL as follows:

CREATE TABLE IF NOT EXISTS `tgmp_affiliates` (
    `a_id` int(8) NOT NULL AUTO_INCREMENT,
    `site_id` int(6) NOT NULL,
    `a_parent` int(8) NOT NULL,
    `a_code` varchar(32) NOT NULL,
    `a_type` varchar(32) NOT NULL,
    `a_platform` varchar(12) NOT NULL,
    PRIMARY KEY (`a_id`),
    UNIQUE KEY `site_id` (`site_id`,`a_parent`,`a_code`,`a_type`,`a_platform`),
    KEY `a_code` (`a_code`),
    KEY `a_parent` (`a_parent`),
    KEY `a_platform` (`a_platform`),
    KEY `a_type` (`a_type`),
    KEY `site_id_2` (`site_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

-- --------------------------------------------------------

--
-- Table structure for table `tgmp_merchants`
--

CREATE TABLE IF NOT EXISTS `tgmp_merchants` (
    `m_id` int(8) NOT NULL AUTO_INCREMENT,
    `site_id` int(6) NOT NULL,
    `m_title` varchar(128) NOT NULL,
    `m_url` text NOT NULL,
    `m_link` varchar(128) NOT NULL,
    `m_favicon` text NOT NULL,
    `m_avg_rating` tinyint(1) NOT NULL,
    `m_hide` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`m_id`),
    UNIQUE KEY `m_title` (`m_title`,`m_link`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

-- --------------------------------------------------------

--
-- Table structure for table `tgmp_prices`
--

CREATE TABLE IF NOT EXISTS `tgmp_prices` (
    `p_id` int(8) NOT NULL AUTO_INCREMENT,
    `site_id` int(6) NOT NULL,
    `p_m_id` int(8) NOT NULL,
    `p_prod_label` varchar(128) NOT NULL,
    `p_gtin` varchar(64) NOT NULL,
    `p_parent` int(8) NOT NULL,
    `p_link` text NOT NULL,
    `p_price` decimal(8,2) NOT NULL,
    `p_delivery` decimal(8,2) NOT NULL,
    `p_currencey` varchar(10) NOT NULL,
    `p_avail` varchar(32) NOT NULL,
    `p_condition` varchar(32) NOT NULL,
    `p_when` datetime NOT NULL,
    `p_type` varchar(32) NOT NULL,
    `p_hide` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`p_id`),
    UNIQUE KEY `p_gtin` (`p_gtin`,`p_m_id`,`p_price`,`p_delivery`),
    KEY `p_price` (`p_price`),
    KEY `p_parent` (`p_parent`),
    KEY `site_id` (`site_id`),
    KEY `p_gtin_2` (`p_gtin`),
    KEY `p_type` (`p_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

I have now amended the query to the following:

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code != '' 
        AND p.p_price = (
            SELECT MIN(tp.p_price) FROM tgmp_prices tp
                WHERE tp.p_m_id = m.m_id
                AND tp.p_parent = '25573'
    )
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p_type = 'games' 
    AND m.m_hide = 0 
GROUP BY m.m_id
ORDER BY p.p_price ASC

This gives the correct number of results and they are very close to the correct set of results but I'm still not getting the lowest price for each merchant.

Answer 1

Modifying slightly your second query, will give you both the merchant id and the lowest price (over all products that pass the conditions - I guess that's what you want):

SELECT p.p_m_id, MIN(p_price) AS min_p_price 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
    AND m.m_hide = 0 
GROUP BY p.p_m_id ;

Then you can join this - as a derived table - to all the tables that you need data from in the results:

SELECT
    m.*, p.*, ga.*                     -- whatever columns you want  
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
JOIN
      ( SELECT p.p_m_id, MIN(p_price) AS p_price 
        FROM tgmp_affiliates ga 
        JOIN tgmp_prices p 
            ON ga.a_code = p.p_gtin 
                AND ga.a_code > '' 
        JOIN tgmp_merchants m 
            ON m.m_id = p.p_m_id 
        WHERE ga.site_id = '34' 
            AND p.site_id = '34' 
            AND ga.a_parent = '25573' 
            AND p.p_type = 'games' 
            AND m.m_hide = 0 
        GROUP BY p.p_m_id 
      ) AS tmp
    ON  tmp.p_m_id = p.p_m_id 
    AND tmp.p_price = p.p_price
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
ORDER BY p.p_price ;