MYSQL order from another table

Question

I have a question.

I have 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 the volgnr field.

Is there a way to do this with mysql?

Answer 1

There are two ways to sort. Ascending and Descending. You did not mention the order. Therefore, I give you both answers with two options:

ORDER RECORD:

 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; 

ORDER DESCRIPTION:

 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 :

ORDER RECORD:

 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; 

ORDER DESCRIPTION:

 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 this helps.

Change 1:

Now I edited the query so that it does not give you duplicates in the results. Try it and let me know how this happens.

Edit 2: Added Group By clause. Try it.

Answer 2

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

Answer 3

 SELECT p.'product_id', p.'name', p.'images_sideview' FROM products p LEFT JOIN product_descriptions d ON p.product_id = d.product_id ORDER BY d.volgnr;