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 ;
Based on your current code, it appears that a product can have at most one match in either customer_price_increment or general_price_increment. With that fact in mind, I would probably try a different approach.
First, I would rewrite the base query returning all the prices like this:
SELECT
p.id AS product_number,
p.name AS product_name,
p.redemption_price,
COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
FROM product AS p
LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id
To filter on sale_price, I would just use the above query as a derived table, so that I could reference the sale_price alias and avoid repeating the entire expression in the WHERE clause:
SELECT *
FROM (
SELECT
p.id AS product_number,
p.name AS product_name,
p.redemption_price,
COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
FROM product AS p
LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id
) AS s
WHERE sale_price > 250
;
I believe you could also make the first query a view and then just select from it filtering the results as necessary:
SELECT *
FROM sale_prices_view
WHERE sale_price > 250
;
Best Answer
You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT
Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1
If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:
First line is for the session, the second is for all new DB Connections.