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.
ON products.product_id = products_pricing.product_id
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
BY products.product_id DESC
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;