I have 4 tables: 'devices', 'inventory', 'discounts', and 'device_discounts_xref'
I want to select all the devices and order them by discounted price. Some phones have discounts some do not.
The query i have is kind of working, but the devices that don't have discounts have a price of NULL. I want the price to be the actual price of the device.
ROUND(`devices`.`price` - `discounts`.`amount`,2) AS `sort_price`
LEFT JOIN `inventory` ON `inventory`.`sku`=`devices`.`sku`
LEFT JOIN `device_discounts_xref` ON `device_discounts_xref`.`device_id`=`devices`.`id`
LEFT JOIN `discounts` ON `discounts`.`id`=`device_discounts_xref`.`discount_id`
ORDER BY `sort_price`