MySQL: Retrieve data from Parent Table if it's Child Table's sum of QtyIn is greater than the sum of QtyOut

Question

Here's my scenario:

Table A     Table B 
IDa = 1     IDb = 1 | IDa = 1 | QtyIn = 2 | QtyOut = 0
IDa = 2     IDb = 2 | IDa = 1 | QTyIn = 0 | QtyOut = 1 
            IDb = 3 | IDa = 2 | QtyIn = 1 | QtyOut = 0
            IDb = 4 | IDa = 2 | QtyIn = 0 | QtyOut = 1

I want to retrieve rows from Table A if the sum of its Table B's QtyIn is greater than the sum of its QtyOut. Table B will be grouped by the column IDa. In this example, IDa = 1 of Table A is the only row that will be retrieved because of it's remaining stock. How do I achieve this?

Answer 1

First you need to select the columns you need so in this case

SELECT COLUMN_NAME 

Next you need to decide what tables you need so in this case you also need table B:

SELECT COLUMN_NAME 
FROM TABLEA, TABLEB

Next you should figure out your join condition in this case:

SELECT COLUMN_NAME 
FROM TABLEA, TABLEB
WHERE TABLEA.IDa = TABLEB.IDa

Finally you want to go to the Aggregate Function and do the calculations you sql should look like the following:

SELECT COLUMN_NAME 
FROM TABLEA, TABLEB
WHERE TABLEA.IDa = TABLEB.IDa
Group by TABLEB.IDa
Having SUM(QtyIn - QtyOut) > 0

So yep that should be all...