Solved

MySQL select, join and math HELP

Posted on 2009-04-07
3
214 Views
Last Modified: 2013-12-12
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
Comment
Question by:fastball1945
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Accepted Solution

by:
oobayly earned 495 total points
ID: 24091897
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
 

Author Comment

by:fastball1945
ID: 24091907
Perfect!  Thanks for the timely response!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24091912
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question