CODEWITHSUNDEEP
mysqlsqlselectjoingroup-by
Falcony
Falcony

Reputation: 35

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

more explain:

table product
----------------------------------
pid |  title   | desc  | content |
----------------------------------
 1  | lumia 920| ..... | ......  |
----------------------------------
 2  | galaxys6 |  .... | ......  |
----------------------------------

table  sub_product
------------------------
 subid |pid|  name  |
------------------------
   1   | 1 | yellow |
------------------------
   2   | 1 | black  |
------------------------
   3   | 2 | 32 GB  |


table stock
-----------------------------------------------
 sid |subid| price | count | inserted_date | 
-----------------------------------------------
  1  |  1  |  100  |   5   |  2015-01-01   |
-----------------------------------------------
  2  |  1  |  150  |   9   |  2015-01-02   |
-----------------------------------------------
  3  |  1  |  100  |   0   |  2015-02-02   |
-----------------------------------------------
  4  |  2  |  111  |   1   |  2015-02-21   |
-----------------------------------------------
  5  |  3  |  50   |   7   |  2015-02-01   |
-----------------------------------------------
  6  |  3  |  10   |   4   |  2015-03-06    |
-----------------------------------------------
  7  |  3  |  400  |   9   |  2015-06-06    |
-----------------------------------------------


table result
------------------------------------------------------------
 pid |subid|  title  | name  | price | count | inserted_date  
------------------------------------------------------------
  1  |  2  |lumia 920| black |  111  |   1   | 2015-02-21
------------------------------------------------------------
  2  |  3  |galaxy s6| 32 GB |  10   |   4   | 2015-03-06
------------------------------------------------------------

as you see in product table we have two items lumia 920 and galaxy s6 and in sub_product we have 3 items that related to products. also in stock I saved all modication of price and count of each item so I want return latest modification of each sub_product as current state of it sub_prodct that has least price as result but if count was zero should return another sub_product with mentioned conditions.

Upvotes: 0

Views: 107

Answers (3)

devpro
devpro

Reputation: 16117

This query will return the all products that have least price and have count greater than 0 and latest records.

SELECT p.pid, sp.subid, p.title, sp.name, s.price, s.count, s.inserted_date
FROM product p
INNER JOIN sub_product sp ON sp.pid = p.pid
INNER JOIN stock s ON s.subid = sp.subid
WHERE s.count > 0
GROUP BY p.title
ORDER BY s.inserted_date DESC, s.price ASC

Upvotes: 1

saeed
saeed

Reputation: 85

    Select * From stock s Join sub_product sp On s.sub_productid =     sp.sub_productid Join product p On p.productid = sp.productid
Where s.counte > 0  And s.date_insert in (Select MAX(date_insert) as d        From stock ss
where s.sub_productid = ss.sub_productid group by sub_productid)

Upvotes: 1

Kostis
Kostis

Reputation: 963

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)

Upvotes: 0

Related Questions