Order By in 2 MYSQL tables

Question

Good morning! I am going through more moments and I would like your help with this problem!

Come on, I’m doing a select in MYSQL where several tables are selected, in opencart to generate an XML for facebook ads.

My XML code looks like this

SELECT DISTINCT t1.product_id as product_idT1, t1.manufacturer_id as manufaturer, t1.price as price, t1.date_added as dataAdd, t1.quantity as quantity, t2.name as nameT2, t2.description as descriptionT2, t3.name as nameT3, t4.name as nameT4, t5.image, t6.name as nameT6, t7.query as urlT7, t7.keyword as keyword 
FROM oc_product t1 
INNER JOIN oc_product_description t2 ON (t1.product_id = t2.product_id) 
INNER JOIN oc_manufacturer t3 ON (t1.manufacturer_id = t3.manufacturer_id) 
INNER JOIN oc_option_value_description t4 
INNER JOIN oc_product_image t5 ON (t1.product_id = t5.product_id) 
INNER JOIN oc_stock_status t6 ON (t1.stock_status_id = t6.stock_status_id) 
INNER JOIN oc_url_alias t7 
WHERE t7.query like CONCAT('%', t1.product_id , '%') 
GROUP BY product_idT1 ASC

Only in this case, the picture table oc_product_image needs to be in DECREASING order because I want the last image.

I am using PHP.

Thank you!

Answer 1

I did it this way:

SELECT DISTINCT t1.product_id as product_idT1, t1.manufacturer_id as manufaturer, t1.price as price, t1.date_added as dataAdd, t1.quantity as quantity, t2.name as nameT2, t2.description as descriptionT2, t3.name as nameT3, t4.name as nameT4, t5.image as imagemT5, t6.name as nameT6, t7.query as urlT7, t7.keyword as keyword, t8.product_id, t8.price as produtoDesconto 
FROM oc_product t1 
LEFT JOIN oc_product_description t2 ON (t1.product_id = t2.product_id) 
LEFT JOIN oc_manufacturer t3 ON (t1.manufacturer_id = t3.manufacturer_id) 
LEFT JOIN oc_option_value_description t4 
LEFT JOIN oc_product_image t5 ON (t1.product_id = t5.product_id) 
LEFT JOIN oc_stock_status t6 ON (t1.stock_status_id = t6.stock_status_id) 
LEFT JOIN oc_product_special t8 ON (t1.product_id = t8.product_id)
LEFT JOIN oc_url_alias t7 
ORDER BY t5.product_id DESC

Return me the following error: #1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'ORDER BY T5.product_id DESC LIMIT 0, 25' at line 10

Can you help me with what I’m doing wrong? Thank you

Answer 2

Do ORDER BY t5.product_id DESC, so order by the order of registration of the product.
The fact that you are not listing all products, must be related to INNER JOINS or even to WHERE.
Exchange the Inners for LEFT JOIN (test only) and remove the Where.
So you will discover what is preventing you from fetching all products.