0

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

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by pritaeas
0

You can add a check like this:

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

Fill in the blanks.

Edited by pritaeas: n/a

0

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?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.