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,,, stk.price,, stk.count, stk.inserted_date
FROM products AS prod
INNER JOIN sub_products AS subProd ON =
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