开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜