Welcome Guest, Not a member yet? Register   Sign In
Mysql request problem

I can not solve the good qty in this request.
She always gives me double the value of qty.
The result is false when I put the following condition:

PHP Code:
$this->db->join('stocks_alert''stocks_alert.id_product = stocks.id_product');
$this->db->having('qty < qty_alert'); 

Here is the complete request:

PHP Code:
$this->db->select('products.id, products.reference, products.label');
$this->db->select("(SUM(CASE WHEN slug IN ('orders','variation','transfer','add') THEN stocks.qty ELSE 0 END) - SUM(CASE WHEN slug ='used' THEN stocks.qty ELSE 0 END)) as qty");
$this->db->join('stocks''stocks.id_product = products.id'); 
$this->db->join('stocks_alert''stocks_alert.id_product = stocks.id_product');
$this->db->having('qty < qty_alert');

I also tried sub-query, but I can not do it.

Would anyone have an idea to put me on the track.
Thank you to all of you. 

Excuse me for my english.

(This post was last modified: 02-13-2019, 09:23 PM by php_rocs.)


Have you been able to run this query directly in MySQL? What does the fully constructed query look like?

I prefer to use the query binding approach when creating my queries (to me it is more readable)... https://codeigniter.com/user_guide/datab...y-bindings

Thank you for your reply.

I did not know the query binding approach. I will deepen it for my developments.

Yes, my request works perfectly.
My problem is that at one point I have to do some qty_alert to compare it to qty. 
At this moment the result qty becomes false doubles its value.

Here is the query built:

PHP Code:
SELECT products.idproducts.referenceproducts.label, (SUM(CASE WHEN slug IN ('orders''variation''transfer''add'THEN stocks.qty ELSE 0 END) - SUM(CASE WHEN slug ='used' THEN stocks.qty ELSE 0 END)) as qtySUM('qty_alert') AS qty_alert 
FROM products JOIN stocks ON stocks
.id_product products.id JOIN stock_alert ON stocks_alert.id_product stocks.id_product 
products.reference LIKE '%%' ESCAPE '!' OR products.label LIKE '%%' ESCAPE '!' GROUP BY products.id HAVING qty qty_alert


You probably have multiple records in one of your joins. I would view each join's query individually to see what values are there. Also, I noticed that you used JOINs instead of LEFT JOINS? Any reason for that?

Thank you for your help.
I have reconsidered all my request and now it is working properly. That's what I did as a request:

SELECT products.id, products.reference, products.label, products.category, products.image, stocks.id_warehouse, (SUM(CASE WHEN slug IN ('orders', 'variation', 'transfer', 'add') THEN stocks.qty ELSE 0 END) - SUM(CASE WHEN slug ='used' THEN stocks.qty ELSE 0 END)) as qty, SUM(CASE WHEN stocks_alert.id_product = stocks.id_product  THEN stocks_alert.qty_alert ELSE 0 END) as qty_alert FROM products LEFT JOIN stocks ON stocks.id_product = products.id LEFT JOIN stocks_alert ON stcks_alert.id_warehouse = stocks.id_warehouse WHERE (products.reference LIKE '%%'  ESCAPE '!' OR  products.label LIKE '%%' ESCAPE '!') GROUP BY products.id HAVING (`qty` < `qty_alert`)


Glad I could help.

Theme © iAndrew 2016 - Forum software by © MyBB