MySQL SUM Query

Question

Hi, I've got two tables.

I'm trying to calculating the SUM quantity of tbl1

tbl1.xid is the primary, while tbl2.xid is the foreign

tbl1

xid pub quantity
1    1    10          
2    1    2      
3    0    1      
4    1    5      

tbl2

id ttype fno xid qnty
1  A       0    1    0
2  A       1    1    3
3  B       1    1    4
4  A       1    2    1 
5  A       1    3    2
6  A       1    4    3
7  A       1    4    1
8  A       0    1    0

We are calculating the sum of tbl1's quantity

1) Whos tbl1.pub is 1 Thus tbl1.xid 3 is removed form the list, for it's pub is 0

Results

tbl1

xid pub quantity
1    1    10          
2    1    2      
4    1    5      

2) AND Who's tbl1 has at least one tbl2.xid who's tbl2.ttype is 'A' and who's tbl2.fno is '0' Thus tbl1.xid 2 & 4 are removed form the list, because none of them have at least one tbl2.xid who's fno is '0' and who's tbl2.ttype is 'A'

Results

parent_tbl1

xid pub quantity
1    1    10          

The final results should be 10

Answer 1

SELECT SUM(quantity) AS Total
FROM   tbl1
WHERE  pub=1
AND    EXISTS
       (SELECT *
       FROM    tbl2
       WHERE   tbl2.ttype = 'A'
       AND     tbl2.fno   = 0
       AND     tbl1.xid   = tbl2.xid
       )