MySQL SUM Query
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
SELECT SUM(DISTINCT src.qnty) as qnty
FROM tbl2 AS src
INNER JOIN tbl1 AS pub
ON src.xid=pub.xid
INNER JOIN tbl2 AS fno
ON pub.xid=fno.xid
WHERE pub.pub=1
AND src.ttype='A'
AND fno.fno=0
精彩评论