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;

| 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;

| 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:

| 3 | 1 | bakra | 120 | 30 |

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

Thanks for your contributions

Please [edit] your question title to something meaningful. It should be something that describes the actual problem you're facing or question you're asking, and should be specific enough to have meaning to a future reader who sees it in a search result. *I have a complicated query* is totally useless in that regard - everyone can say their query is *complicated*, whether it actually is or not. Your question is not too clear, either; some sample data from both tables and the output you'd like to get from that sample data with your query would help. — Ken White, Jun 16 '16 at 00:21

score 0 · Answer 1 · answered Jun 16 '16 at 00:41

0

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

answered Jun 16 '16 at 00:41

Andrews B Anthony

1,381
9
27

Not helpfull, that produces the same result I get : 4rows and I want to get only 2 rows as I asked. – Imededdine Fargi Jun 16 '16 at 01:35

ck1 · Answer 2 · 2016-06-16T01:20:50.627

0

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

edited Jun 16 '16 at 01:20

answered Jun 16 '16 at 00:44

ck1

5,243
1
21
25

No. Iwant to get all MatPr that have the condition threshold_stock > Qte_Tot – Imededdine Fargi Jun 16 '16 at 01:18

score 0 · Answer 3 · answered Jun 16 '16 at 02:26

0

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

answered Jun 16 '16 at 02:26

Yosra Hamza

529
3
5

Or you can order by MvtStock_MatPr.id desc as the DateMvt column is not datetime and can be repeated in more than one row – Yosra Hamza Jun 16 '16 at 08:03
That was so close... This query gets two rows but the unneeded ones: rows with MvtStoc_MatPr.id 1and 2 .What I want to get is the rows with MvtStock_MatPr.id 3 and 7 . – Imededdine Fargi Jun 16 '16 at 09:10
I just tried it and it gets the MvtStock_MatPr.id ids 3 and 7 but the column is renamed to 'id' instead of MvtStock_MatPr.id, can you paste the results to see what data I have wrongly inserted in the tables? – Yosra Hamza Jun 16 '16 at 09:25
| id | idMatPr | De|signation threshold_stock| Qte_Tot | | 1 | 1 | bakra | 120 | 10 | | 2 | 2 | zipper | 130 | 20 | 2 rows in set (0.00 sec) – Imededdine Fargi Jun 16 '16 at 09:38
I can't get what's wrong, but I am getting the results with 3 and 7, maybe something in the table creation is different, here is a sqlfiddle http://sqlfiddle.com/#!9/7e916/1 – Yosra Hamza Jun 16 '16 at 10:12
You forgot to paste the string, are the tables different from the ones in the fiddle? – Yosra Hamza Jun 16 '16 at 11:14
I believe you but when I tried on the REAL environment = MySQL 5.7 it doesn't deliver the expected result as in SqlFiddle ! – Imededdine Fargi Jun 16 '16 at 11:42

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

3 Answers3