I have a challenging MySQL SELECT query that involves stock and threshold bondary

Question

I Have a MySQL DataBase which contains two tables linked with a foreign key:

MatPr(id, Designation, threshold_stock)

And

MvtStock_MatPr(id, Qte_Mvt, Qte_Tot,idMatPr)

What I want is to get the last Qte_tot, Designation,threshold_stock where threshold_stock>qte_tot I tried this code and did not work:

SELECT MvtStock_MatPr.id,idMatPr, Nom, threshold_stock, Qte_Tot 
FROM MvtStock_MatPr, MatPr 
WHERE MatPr.id=MvtStock_MatPr.idMatPr AND  threshold_stock>Qte_Tot

Here is Sample Data set:

mysql> SELECT MvtStock_MatPr.id,idMatPr, Designation, threshold_stock, Qte_Tot FROM MvtStock_MatPr, MatPr WHERE MatPr.id=MvtStock_MatPr.idMatPr AND threshold_stock>Qte_Tot ORDER

BY id,idMatPr;

| id | idMatPr | Dsignation| threshold_stock| Qte_Tot |

| 1 | 1 | bakra | 120 | 10 |

| 2 | 2 | zipper | 130 | 20 |

| 3 | 1 | bakra | 120 | 30 |

| 7 | 2 | zipper | 130 | 50 |

4 rows in set (0.00 sec)

mysql> SELECT * FROM MatPr;

| id | Designation| threshold_stock|

| 1 | bakra | 120 |

| 2 | zipper | 130 |

2 rows in set (0.00 sec)

mysql> SELECT * FROM MvtStock_MatPr;

| id | DateMvt | Qte_Mvt | Qte_Tot | idMatPr |

| 1 | 2016-01-01 | 10 | 10 | 1 |

| 2 | 2016-02-02 | 20 | 20 | 2 |

| 3 | 2016-03-03 | 20 | 30 | 1 |

| 4 | 2016-03-03 | 100 | 130 | 1 |

| 5 | 2016-03-03 | 50 | 180 | 1 |

| 6 | 2016-03-03 | 20 | 200 | 1 |

| 7 | 2016-03-05 | 30 | 50 | 2 |

7 rows in set (0.00 sec)

What I would like to get is:

| id | idMatPr | Dsignation| threshold_stock| Qte_Tot

| 3 | 1 | bakra | 120 | 30 |

| 7 | 2 | zipper | 130 | 50 | +----+---------+--------+-------+---------+

Thanks for your contributions

Answer 1

Use the below query

Select m1.id,m1.idMatpr,m.threshold_stock,m.Qte_Tot 
From MvtStock_Matpr m1 join Matpr m
On m1.idMatPr=m.id and m.threshold_stock>m1.Qte_Tot

Answer 2

I'm not sure that I completely follow what your problem is: is it selecting just one row from a result set? If so, does a subquery fix your problem?

SELECT *
FROM MatPr
WHERE ID = (
  SELECT idMatPr 
  FROM MvtStock_MatPr, MatPr 
  WHERE MatPr.id = MvtStock_MatPr.idMatPr 
    AND threshold_stock > Qte_Tot
  LIMIT 1
)

If you want to select just the rows from MatPr, does this work for you?

SELECT MatPr.*
FROM MvtStock_MatPr, MatPr 
WHERE MatPr.id = MvtStock_MatPr.idMatPr 
  AND threshold_stock > Qte_Tot

http://sqlfiddle.com/#!9/d4ef50/2

Answer 3

Try this query:

select * from 
   ( SELECT MvtStock_MatPr.id,idMatPr,Designation, threshold_stock, Qte_Tot 
     FROM MvtStock_MatPr join MatPr 
     on MatPr.id=MvtStock_MatPr.idMatPr 
     where threshold_stock>Qte_Tot 
     order by DateMvt desc 
   ) T 
 group by T.idMatPr