CODEWITHSUNDEEP
mysqlsql-order-by
lth159856
lth159856

Reputation: 49

MYSQL order by another tables column but needs some (and) conditions

Here are the database tables and some records enter image description here

What I want to achieve is to select everything from products sort conditions (ORDER BY) are first, products_campaign end_date ASC. Second, products score DESC BUT the WHERE conditions are products_campaign enable equals to 1

So as the result. I want to display all products as sort with first it has active campaign(enable) sort by end_date if not then sort by score. Mention: some products do not have records in products_campaign (e.g.productID:104). I tried use LEFT JOIN but the productID 104 wouldn't be selected into result list.

Any help? Best Regards

Upvotes: 0

Views: 28

Answers (1)

PK20
PK20

Reputation: 1066

Check if this helps

SELECT A.* 
FROM PRODUCTS A LEFT OUTER JOIN 
  (SELECT * FROM PRODUCTS_CAMPAIGN WHERE ENABLE = 1) AS B
ON A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY B.END_DATE ASC, A.SCORE DESC

Upvotes: 1

Related Questions