Felipe Mathais Felipe Mathais -4 years ago 100
SQL Question

Order by with complex relationship

I have a complex and confused scenary.

I need order by prices. But, I have a logic on prices...

cad_product
id
title
store_id

cad_store
id
business_model

//This table can be associated to 1 or more products...
cad_sku
id
product_id

cad_price_sku
id
sku_id
retail
sale_retail
wholesale
sale_wholesale


I need sql order by prices, but the same product can contain 1 or more SKUs, and the next SKU, can contain a less price than the first. That is, I need pass for each product SKU and return that minor price.

Besides that, I've a business_model logic. Where a product can be shown through your B2C prices (retail and sale_retail), B2B prices (wholesale and sale_wholesale) e BOTH (both b2b and b2c prices).

I've tried something similar to this.

SELECT cad_product.title, skuP.retail, skuP.sale_retail, skuP.wholesale, skuP.sale_wholesale
FROM cad_product
INNER JOIN cad_sku sku ON cad_product.id = sku.product_id
INNER JOIN cad_price_table_sku skuP ON sku.id = skuP.sku_id
INNER JOIN cad_store store ON store.id = cad_product.store_id
WHERE cad_product.deleted_at IS NULL
AND ((skuP.sale_retail <> 0 AND skuP.retail <> 0))
ORDER BY skuP.sale_retail asc, skuP.retail asc, skuP.sale_wholesale asc, skuP.wholesale asc;


And this result...

+-----------------------------+--------+-------------+-----------+----------------+
| title | retail | sale_retail | wholesale | sale_wholesale |
+-----------------------------+--------+-------------+-----------+----------------+
| Produto de teste - Loja B2C | 1.50 | 1.00 | 0.75 | 0.50 |
| Produto de teste - Loja B2C | 20.00 | 18.00 | 80.00 | 0.00 |
| Novamente TESTE | 125.23 | 4892.13 | 800.00 | 400.00 |
+-----------------------------+--------+-------------+-----------+----------------+

Answer Source

So if I am understanding you correctly, you want to order by the minimum lowest price of the 4 price columns.

That is done this way

SELECT cad_product.title, skuP.retail, skuP.sale_retail, skuP.wholesale, skuP.sale_wholesale 
FROM cad_product
INNER JOIN cad_sku sku ON cad_product.id = sku.product_id
INNER JOIN cad_price_table_sku skuP ON sku.id = skuP.sku_id
INNER JOIN cad_store store ON store.id = cad_product.store_id
WHERE cad_product.deleted_at IS NULL
AND ((skuP.sale_retail <> 0 AND skuP.retail <> 0))
ORDER BY LEAST(skuP.sale_retail, skuP.retail, skuP.sale_wholesale, skuP.wholesale) asc;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download