MYSQL Order from another Table

Question

I have a question.

I have here 2 tables

table 1 : products

product_id , name , images_sideview

table 2 : product_descriptions

product_id , volgnr

I want to sort them with the numbers from Table 2 and volgnr field.

Is there a way to do this with mysql ?

Answer 1

There are 2 ways to sort. Ascending order and Descending order. You have not mentioned the order. So I am providing you both answers with 2 variations:

ASCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.product_id ASC, table2.volgnr ASC;

DESCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.product_id DESC, table2.volgnr DESC;

If you want to tell MySQL to first sort FIRST by volgnr and then by product_id:

ASCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.volgnr ASC, table2.product_id ASC;

DESCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.volgnr DESC, table2.product_id DESC;

Hope that helps.

Edit 1:

I have now edited the query so that it does not give you duplicates in results. Try it out and let me know how that goes.

Edit 2: Added Group By clause. Try this out.

Answer 2

select *
from products t1
inner join product_descriptions t2 on t1.product_id = t2.product_id
order by t2.volgnr