[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

MySQL select, join and math HELP

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.
SELECT
	`devices`.*,
	`inventory`.`quantity`,
	ROUND(`devices`.`price` - `discounts`.`amount`,2) AS `sort_price`
FROM
	`devices`
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`
WHERE
	`devices`.`active`='1'
ORDER BY `sort_price`

Open in new window

0
fastball1945
Asked:
fastball1945
1 Solution
 
oobaylyCommented:
Use IFNULL for the discounts. Basically, if the discounts.amount == null, use 0 instead:
SELECT
        `devices`.*,
        `inventory`.`quantity`,
        ROUND(`devices`.`price` - IFNULL(`discounts`.`amount`, 0),2) AS `sort_price`
FROM
        `devices`
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`
WHERE
        `devices`.`active`='1'
ORDER BY `sort_price`

Open in new window

0
 
fastball1945Author Commented:
Perfect!  Thanks for the timely response!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
try this:
SELECT
      `devices`.*,
      `inventory`.`quantity`,
      ROUND(`devices`.`price` - COALESCE(`discounts`.`amount`,0) ,2) AS `sort_price`
FROM
      `devices`
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`
WHERE
      `devices`.`active`='1'
ORDER BY `sort_price`

Open in new window

0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now