CODEWITHSUNDEEP
mysql
David Richards
David Richards

Reputation: 5

Sum Comparison from another MySQL Table

I have two tables listed below. Column 3 of table 2 is the FK to column 1 of table 1. Col_2 or table 1 shows how much stock I have bought total. And table 2 column 2 shows how much of that stock has sold in one large chunk. So the first two records of table 2 show that I have sold 250 and 200 of product one. This will leave me with 50 units of product 1.

My question is, how can I build a query that will pull only the products for which I still have stock for. In the example below, I would show only the products for which stock was greater than 0. If this sounds too complicated, I am open to suggestions on how this could be performed better.

Table 1
Col_1   Col_2
======  =====
1       500
2       500
3       400

Table 2
Col_1   Col_2   Col_3 (FK table1 Col_1)
======  ======  =======================
1       250     1
2       200     1
3       400     2
4       400     3

Desired result
Col_1(t1)   Col_2(remaining stock)
========    ======================
1           50
2           100

Upvotes: 0

Views: 30

Answers (1)

Rumbleweed
Rumbleweed

Reputation: 370

Something like this might work:

SELECT Col_1, Col_2 - Sold as Remaining_Stock FROM Table_1 JOIN
(SELECT Col_3, SUM(Col_2) as Sold FROM Table_2 GROUP BY Col_3) as Table_Sold
ON Table_1.Col_1 = Table_Sold.Col_3 AND Col_2 > Sold

The subquery totals up your sales, then a simple join with your inventory should do it.

Upvotes: 1

Related Questions