NTFM NTFM - 16 days ago 6
MySQL Question

mysql join select lowest price set flag

I have a two table join where one table holds product data and the other pricing data including multiple discount pricing. Not every product has multiple entries in the pricing table, as some products only have a single unit price.
What I'm trying to figure out is in my select how to set a flag that lets me know if a product has multiple pricing available.

The code I have now (see below) returns the products and finds the lowest price for each product. However like I said not all products have a "lowest price". I'm trying to determine if the price coming with the product is a single unit price or a lowest price.

SELECT products.*
, products_pricing.*
FROM products
LEFT
JOIN products_pricing
ON products.product_id = products_pricing.product_id
LEFT
JOIN products_pricing AS filter
ON products_pricing.product_id = filter.product_id
AND products_pricing.qty_price > filter.qty_price
WHERE filter.product_id IS NULL
AND products.product_active > 0
ORDER
BY products.product_id DESC

Answer

From your description I gather that every product has at least one price in products_pricing, so no need for an outer join.

Rather than using the anti join pattern, I would simply join with the aggregated product. Use a comparision of min/max or a count to get to your flag then.

select 
  p.*,
  pp.*,
  case when ppm.min_qty_price = ppm.max_qty_price then 'single' else 'multi' end as flag
from products p
join products_pricing pp on pp.product_id = p.product_id 
join 
(
  select product_id, min(qty_price) as min_qty_price, max(qty_price) as max_qty_price
  from products_pricing
  group by product_id
) ppm on ppm.product_id = pp.product_id
      and ppm.min_qty_price = pp.qty_price
where p.product_active > 0 
order by p.product_id desc;