SQL Query Difference between Table 1 compared to Sum of Table2 Child Items Referenced by Foreign Key

Question

Let's say I have 2 tables:

Table1

 ID    Name    Quantity
 1     Pencil        12
 2     Pen           35
 3     Ruler         50

Table 2

 ID    Name    Quantity   FK_Table1
 1     Pencil         6           1
 2     Pencil         6           1
 3     Pen           10           2
 4     Pen            5           2
 5     Pen            5           2
 6     Pen            5           2
 7     Ruler         12           3
 8     Ruler         12           3
 9     Ruler         12           3
10     Ruler         12           3

• I need to make a query that selects only the row in table1 from quantity column where it doesn't match with the sum of all quantity column in Table2 that are having the same FK_Table1 value

  (e.g. Pencil will not be selected since its quantity is 12 in table1, the same as the sum of quantities of row ID# 1 and 2 in table2).

• I need to make a query that selects the table2 rows instead of table1, but with same rules applied

How can I do this?

Answer 1

Try below query :

select * from table1 t1
where t1.Quantity <> (select sum(t2.Quantity) from table2 t2 where t2.FK_Table1 = t1.ID);

For getting records of Table 2:

select * from table2 where FK_Table1 IN(
  select t1.ID from table1 t1
  where t1.Quantity <> (
    select sum(t2.Quantity) from table2 t2 where t2.FK_Table1 = t1.ID
   )
);