1

I have a 2 tables products and brands.

Products    

+------------+-------------------------------------+------------+-------+
| product_id | title                               | brand_name | price |
+------------+-------------------------------------+------------+-------+               
| 1          | Pacific Evolution 26" Mountain Bike | Pacific    | 100   |
+------------+-------------------------------------+------------+-------+
| 2          | Schwinn Riverside Cruiser           | Schwinn    | 200   |
+------------+-------------------------------------+------------+-------+                   

Brands
+-----------+-------------+----------+
| brand_id  | brand_name  | discount |
+-----------+-------------+----------+
| 22        | Schwinn     | 10       |
+-----------+-------------+----------+

The price for a product changes if a brand has a discount. (In my table example Schwinn brand has 10% discount)

How can I output a list of products in ascending order by price in my case?

Leigh
  • 28,765
  • 10
  • 55
  • 103
Dmitry
  • 4,143
  • 10
  • 46
  • 57

2 Answers2

4

You really should have brand_id in products table instead of brand_name. This way if you change your brand name, you'll have to change it in both tables.

For this and other advantages of database normalization see this question: What is Normalisation (or Normalization)?

As it is, to get the prices you can do (having the brand_id, you would join by it instead of brand_name):

SELECT p.product_id, 
       p.title, 
       p.brand_name, 
       p.price,
      (p.price*(1-ifnull(b.discount,0) * 0.01)) AS discounted_price
FROM products p
LEFT JOIN brands b
    ON p.brand_name = b.brand_name
ORDER BY discounted_price ASC

You can see it working in this fiddle

Community
  • 1
  • 1
Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
3

IMHO your table PRODUCTS is not correct, because you must use a field to link field brand_id of brands table.

Another advice, IMHO field of discount I stored in decimal field in notation 0.1 (to express 10 %).

So, you can write a query with JOIN to obtain your result, in this way (I assume you'll change your reference between two tables)

SELECT p.product_id, p.title, b.brand_name, p.price,
(p.price * (1 - b.discount * 0.01)) as discounted_price
FROM products p
JOIN brands b
on p.brand_id = b.brand_id
order by (p.price * (1 - b.discount * 0.01)) asc
Joe Taras
  • 15,166
  • 7
  • 42
  • 55