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