Mysql request problem |
Hello,
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->select_sum('qty_alert'); Here is the complete request: PHP Code: $this->db->select('products.id, products.reference, products.label'); 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.
@alain,
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.id, products.reference, products.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 qty, SUM('qty_alert') AS qty_alert
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: Code: 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`) |
Welcome Guest, Not a member yet? Register Sign In |