• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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