0

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.

Falcony
  • 25
  • 5
  • It always helps tremendously if you actually include plain text tables in your question, as many in the community will use them to create SQL demonstrations when answering. Best to paste in a formatted table (with whitespace, pipes, commas, whatever) highlight it and `ctl-k` or use the `{}` editor toolbar button to format it as a code block. – Michael Berkowski Dec 31 '15 at 21:10
  • Multiple record with subproduct id or unique record? With item count – devpro Dec 31 '15 at 21:11
  • All products with least price? when that happens? Otherwise prices are same, only one product exist such that it has least price. – TRiNE Dec 31 '15 at 21:12
  • Least price means u need to price ASC ? – devpro Dec 31 '15 at 21:17
  • in the stock table store all modification of a product , if the price,count or discount had changed I insert a new record. so I want all product from product table that based on latest sub product modification that has least price and count greater than zero. – Falcony Dec 31 '15 at 21:20
  • @devpro in result table it's shown. I want list of product. based on pid – Falcony Dec 31 '15 at 21:22
  • @TRiNE list of products and group by them based on pid and in each group by record should be least price record! – Falcony Dec 31 '15 at 21:25
  • Why are you selecting the row with price 160 instead of 100? – Barmar Dec 31 '15 at 21:27
  • What exactly do you mean with least price? All products with least price, compared to what?? – Kostis Dec 31 '15 at 21:29
  • @Barmar because it has newest date, I want latest records.in fact record with price 160 inserted after price 100. – Falcony Dec 31 '15 at 21:37
  • See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with the latest date for each subproduct. Then join that with the subproduct and product tables. – Barmar Dec 31 '15 at 21:40
  • @Kostis compare with themselves.... in stock table insert multiple sub product that each sub product related to a product... so i want that record with min price and other condition that mentioned in abov – Falcony Dec 31 '15 at 21:40
  • is there any success?? @esmaeilbahrani – devpro Jan 01 '16 at 05:45
  • @devpro unfortunately no. I edited and more explained question. – Falcony Jan 01 '16 at 07:54
  • Ohh, i have updated the group by column plz try – devpro Jan 01 '16 at 08:01
  • @esmaeilbahrani: thanks for update, i have updated my answer and its perfect, i have test by creating dummy table... try it – devpro Jan 01 '16 at 08:33
  • @esmaeilbahrani it is a bit confusing. You say you want the last modification with the least price, but in your example for subid i see you choose row with price 10. The date of this row is not the most recent. So what are the priorities??? 1) most recent date 2) lowest price or 1) lowest price 2) and the corresponding date – Kostis Jan 02 '16 at 17:37

3 Answers3

1
    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)
saeed
  • 85
  • 10
1

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
devpro
  • 16,184
  • 3
  • 27
  • 38
  • it's not my ask, maybe I explain it badly! I edited post,please read it again thanks so much – Falcony Jan 01 '16 at 08:13
  • @esmaeilbahrani: thanks for update, i have updated my answer and its perfect, i have test by creating dummy table... try it – devpro Jan 01 '16 at 08:33
  • thanks @devpro , I try it but it's not my request! between two common sub_product should selected least price! in above example you can see ,in table stock at between subid 1 and 2 we choose 2 because it has lesser value than subid 1 – Falcony Jan 01 '16 at 09:22
  • yes you are right, i am checking it happened due to group by @esmaeilbahrani – devpro Jan 01 '16 at 09:23
0

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)
Kostis
  • 953
  • 9
  • 21