CodeIgniter Forums

Full Version: Mysql request problem
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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.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 ='); 
$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.

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)...
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 JOIN stock_alert ON stocks_alert.id_product stocks.id_product 
products.reference LIKE '%%' ESCAPE '!' OR products.label LIKE '%%' ESCAPE '!' GROUP BY 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.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 = LEFT JOIN stocks_alert ON stcks_alert.id_warehouse = stocks.id_warehouse WHERE (products.reference LIKE '%%'  ESCAPE '!' OR  products.label LIKE '%%' ESCAPE '!') GROUP BY HAVING (`qty` < `qty_alert`)

Glad I could help.