This question has already been answered:

There are two tables

enter image description here

I fulfill request

SELECT p.image FROM oc_product p WHERE product_id = '31' UNION (SELECT pim.image FROM oc_product_image pim WHERE product_id = '31' ORDER BY `sort_order` ASC)

Those. sorting in the second query does not work, or it works, but in the query it still displays the unsorted

Reported as a duplicate by participants Small , Mike , user194374, Denis Bubnov , Alex December 31, '16 at 6:18 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

1 answer 1

To obtain the desired result, select -1 : -1 AS sort_order as the value of the sort field from the first table:

 (SELECT p.image, -1 AS sort_order FROM oc_product AS p WHERE product_id = '31') UNION (SELECT pim.image, sort_order FROM oc_product_image pim WHERE product_id = '31') ORDER BY `sort_order` ASC

or to select only one field using the subquery:

 SELECT d.image FROM ( SELECT p.image, -1 AS sort_order FROM oc_product AS p WHERE product_id = '31' UNION SELECT pim.image, sort_order FROM oc_product_image pim WHERE product_id = '31' ) as d ORDER BY d.sort_order ASC
  • Many thanks, it worked out! - Dmitry Moskvitin
  • @DmitryMoskvitin please :) You can mark the answer as correct. - teran
  • >> @DmitryMoskvitin please :) You can mark the answer as correct. - teran 3 minutes ago - And how to do it? - Dmitry Moskvitin
  • For selecting one field does not work, displays without sorting - Dmitry Moskvitin
  • Although no, I am mistaken, to output one field also works correctly, I apologize. - Dmitry Moskvitin