CODEWITHSUNDEEP
mysql
Ismail Gunes
Ismail Gunes

Reputation: 558

Get the sum by comparing between two tables

I have to tables ProdBiscuit As tb and StockData As sd , I have to get the sum of the quantity in StockData (quantite) with the condition of if (sd.status>0 AND sd.prodid = tb.id AND sd.matcuisine = 3)

Here is my sql query

 SELECT tb.id, tb.nom, tb.proddate, tb.qty, tb.stockrecno 
 FROM ProdBiscuit AS tb
 JOIN 

(SELECT id, prodid, matcuisine, status, SUM(quantite) AS rq FROM StockData) AS sd
 ON (tb.id = sd.prodid AND sd.status > 0 AND sd.matcuisine = 3) LIMIT 25 OFFSET @Myid

This gives me no rows at all ?

There is only 3 rows in ProdBiscuit and 11 rows in Stockdata and there is only 2 rows in StockData good with the condition.

And as shown in the picture there is only two rows which give the condition.

Image of two tables

What is wrong in my query ?

PS: The green lines on the image shows the condition in my query.

Upvotes: 0

Views: 50

Answers (2)

Ismail Gunes
Ismail Gunes

Reputation: 558

SELECT tb.id, tb.nom, tb.proddate, tb.qty, tb.stockrecno, SUM(quantite)  As rq

FROM ProdBiscuit AS tb 

JOIN StockData AS sd  ON (tb.id = sd.prodid AND sd.status > 0 AND sd.matcuisine = 3)
OR (tb.id = sd.prodid AND sd.status = 0 AND sd.matcuisine = 3)  
GROUP BY tb.id, tb.nom, tb.proddate, tb.qty, tb.stockrecno

This query gives me this

ID  NOM                    QTY      RQ
1   Biscuit Chocolat       100      100
2   Biscuit Moutarde       120      226
3   Biscuit Pain Epice     100      100

AND Happy End with the help of @AgRizzo I did a little modification and add for having the exact display:

SELECT tb.id, tb.nom, tb.proddate, tb.qty, tb.stockrecno, 

SUM(IF((status>0 AND tb.id=sd.prodid),quantite,0))  As rq FROM ProdBiscuit AS tb 

JOIN StockData AS sd  ON (tb.id = sd.prodid AND sd.status > 0 AND sd.matcuisine = 3)
OR (tb.id = sd.prodid AND sd.status = 0 AND sd.matcuisine = 3)  
GROUP BY tb.id, tb.nom, tb.proddate, tb.qty, tb.stockrecno



ID  NOM                    QTY      RQ
1   Biscuit Chocolat       100      100
2   Biscuit Moutarde       120       14
3   Biscuit Pain Epice     100      100

Code for displaying the "rq" is:

QtyLst.Items.Add((Convert.ToInt16(reader["qty"])- Convert.ToInt16(reader["rq"])).ToString());

Upvotes: 0

AgRizzo
AgRizzo

Reputation: 5271

Please check this. If you aren't getting the results you want, go to SQLFiddle.com and create sample tables with data to make it easier to figure out what you want.

SELECT tb.id, tb.nom, tb.proddate, tb.qty, tb.stockrecno, SUM(sd.quanite)
FROM ProdBiscuit AS tb
JOIN StockData AS sd
  ON tb.id = sd.prodid
WHERE sd.status > 0 AND sd.matcuisine = 3
GROUP BY tb.id, tb.nom, tb.proddate, tb.qty, tb.stockrecno

Upvotes: 2

Related Questions