MySQL SUM Query

[n_kip]

Greetings, I've got a query that I'm struggling with, this is the first time that I am encountering this type of query. I have two table as shown below.

xid is the primary key in parent_tbl1, while xid is the foreign key in child_tbl2

parent_tbl1

xid pub 
1    1    
2    1    
3    0    
4    1

child_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       1    1    1

Below is the exlanation of the query in parts, which will then need to make up the whole query.

I need the SUM of qnty in child_tbl2:

1) Who's parent's pub is '1' Therefore, id 5 is eliminated from child_tbl2, this is because xid 3 is 0 in parent_tbl1

Results: child_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
6  A       1    4    3
7  A       1    4    1
8  A       1    1    1

2) AND who's parent table has ttype 'A' in the child table Therefore, id 3 is eliminated from the existing results because id 3's ttype is B

Results: child_tbl2

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

3) AND who's parent has '0' as one it's fno's in the child_tbl2 Therefore, id 4, 6 & 7 are eliminated from the existing results, this is because 0 was not found in one of their fno's, while 0 was found as one of xid 1's fno

Results: child_tbl2

id ttype fno xid qnty
1  A       0    1    0
2  A       1    1    3
8  A       1    1    1

The answer for the query should be 4

Below is what i've got.

SELECT sum(child_tbl2.qnty), parent_tbl1.xid, parent_tbl1.pub, child_tbl2.ttype, child_tbl2.fno, child_tbl2.xid 
FROM parent_tbl1, child_tbl2
WHERE parent_tbl1.xid = child_tbl2.xid
AND parent_tbl1.pub = '1'
AND child_tbl2.ttype = 'A'

AND child_tbl2.fno ?

If it is possible, I do not know how to tell the dbms (MySQL) to check if Zero is one of the fno's. If I say "AND child_tbl2.fno = '0'", I will be saying that the result's fno should be 0. I do not want that, I need zero to be one of the fno's in order for the query to SUM all the qnty in that particular xid

[pritaeas]

You can add a check like this:

WHERE child_tbl2.xid NOT IN (SELECT xid FROM child_tbl2 WHERE ...)

Fill in the blanks.

[n_kip]

You can add a check like this:

WHERE child_tbl2.xid NOT IN (SELECT xid FROM child_tbl2 WHERE ...)

Fill in the blanks.

Hi,
I'm not sure where this check should go, is it at the end of the query?

[pritaeas]

Yes, AND instead of WHERE.