esmaeil bahrani esmaeil bahrani - 4 years ago 186
SQL Question

Implementing a complex query in MySQL

I have 3 tables:


  1. table products

  2. table sub products

  3. table stock.



I want to join these tables like in the result table below. In fact, I want All products that have least price and have count greater than zero and inserted as latest records!

How can I do the query?

schema of my tables

Answer Source

Try something like this:

SELECT prod.title, prod.desc, subProd.spid, subProd.pid, subProd.name, stk.price, stk.discount, stk.count, stk.inserted_date
FROM products AS prod
INNER JOIN sub_products AS subProd ON prod.pid = subProd.pid
INNER JOIN stock AS stk ON  subProd.spid = stk.spid 
AND stk.count > 0 
AND stk.spid = (select spid from stock order by inserted_date desc limit 1)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download