CODEWITHSUNDEEP
mysqlsql
Kareem Mohamed
Kareem Mohamed

Reputation: 251

MySql query to order by existence in another table

I have three tables (products, product_info and specials)

products(id, created_date)
product_info(product_id, language, title, .....)
specials(product_id, from_date, to_date)

product_id is foreign key which references id on products

When searching products I want to order this search by products that are specials...

Here's my try

SELECT products.*, product_info.* FROM products
INNER JOIN product_info ON product_info.product_id = products.id
INNER JOIN specials ON specials.product_d = products.id
WHERE product_info.language = 'en'
AND product_info.title like ?
AND specials.from_date < NOW()
AND specials.to_date > NOW()
ORDER BY specials.product_id DESC, products.created_at DESC

But the result is only special products..

Upvotes: 2

Views: 111

Answers (3)

SQB
SQB

Reputation: 4078

SELECT 
   products.*, 
   product_info.*
FROM products
JOIN product_info ON product_info.product_id = products.id
LEFT JOIN specials 
   ON specials.product_d = products.id AND NOW() BETWEEN specials.from_date AND specials.to_date
WHERE product_info.LANGUAGE = 'en'
  AND product_info.title LIKE ?
ORDER BY 
   specials.product_id DESC NULLS LAST,
   products.product_id DESC,
   products.created_at DESC;

Since you don't have specials for all products, you have to do an outer join instead of an inner.

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

If not every product is in specials table, you should do a LEFT JOIN with specials instead and put the validations of the dates of specials in the ON CLAUSE. Then you order by products.id but put the specials first, by validating it with a CASE WHEN:

SELECT products.*, product_info.*
FROM products
INNER JOIN product_info ON product_info.product_id = products.id
LEFT JOIN specials ON specials.product_d = products.id
                  AND specials.from_date < NOW()
                  AND specials.to_date > NOW()
WHERE product_info.LANGUAGE = 'en'
  AND product_info.title LIKE ?
ORDER BY CASE 
    WHEN specials.product_id IS NOT NULL THEN 2
    ELSE 1
    END DESC,
  products.id DESC,
  products.created_at DESC

Upvotes: 2

Damodaran
Damodaran

Reputation: 11065

Try this

SELECT products.*, product_info.* FROM products
INNER JOIN product_info ON product_info.product_id = products.id
INNER JOIN specials ON specials.product_d = products.id
WHERE product_info.language = 'en'
AND product_info.title like ?
AND specials.from_date < NOW()
AND specials.to_date > NOW()
ORDER BY specials.product_id DESC,products.id DESC, products.created_at DESC

Upvotes: 0

Related Questions