Sorting on multiple fields?

Question

MySQL

Hello. I started writing a simple Internet store on yii, a question arose. What if there are multiple price lists. How to sort by 2 fields? I will give an example of what exactly needs to be sorted.
Here is the request:

select p.name, price1.value, price2.value
from product p
join price price1 on p.id=price1.product_id
left join price price2 on p.id=price2.product_id
order by price?.value asc

And how the table looks like:
p.name | price1.value | price2.value
Iron | 5000 |
Note | 25000 |
Cracked laptop | 25000 | 18000
Iron without fork | 5000 | 1000
And here I need to sort by price (from min to max), so that the product.is_broken attribute is taken into account:
1. Iron without a fork
2. Iron
3. Cracked laptop
4. Laptop
What is the best way to do this? Can you suggest the correct architecture...
UPD added a link to sql fiddle

Answer 1

As I understand it, first the sorting is done by price1.value, then, where the values ​​matched, we sort by price2.value
, in this case, you can simply list them in ORDER BY

select p.name, price1.value, price2.value
from product p
join price price1 on p.id=price1.product_id
left join price price2 on p.id=price2.product_id
order by price1.value asc, price2.value asc

True, there is one thing but, with LEFT JOIN, the missing records take on the NULL value, which will be at the top when sorted