Merge 2 tables and fetch data one by one

Question

I have two tables: product & offers.

products has (prod_id, category_id, prod_name, brand, price, status)
offers has (offer_id, prod_id, offer_type, discount_amt, validity)

I want to display products on a page, 20 products at a time.

The problem that I want to display all the products from the offers table arranged by descending offer_id and then display products from the products table except those in the offers table within that 20 limit.

How can I achieve that?

Answer 1

Seems pretty straightforward.

SELECT * from products left join offers on prod_id=prod_id
ORDER BY offer_id DESC LIMIT 20;

See it here: http://sqlfiddle.com/#!2/dff49/3/0