435,608 Members | 3,801 Online
Need help? Post your question and get tips & solutions from a community of 435,608 IT Pros & Developers. It's quick & easy.

# seek tips on query

 P: n/a Here are two tables A.id,A,val 1,3 1,4 1,7 2,3 2,4 3,8 4,8 5,8 5,9 B.val, B.foo 3,3 4,3 7,3 8,2 9,2 I want to get the id values of A where all the A.vals are represented by B.vals. I'm seeking a join that would return 1 and 5. 1 has vals 3,4,7 5 has vals 8,9 2 is not returned because it does not have a 7. 4 is not returned because 4,8 is not a row of B. The value of B.foo is the number of items that must match. 3,4,7 are treated a a group and consists of 3 rows. 8,9 are treated as a group and consists of 2 rows. Thanks. Mar 20 '07 #1
4 Replies

 P: n/a gi*******************@yahoo.com wrote: Here are two tables A.id,A,val 1,3 1,4 1,7 2,3 2,4 3,8 4,8 5,8 5,9 B.val, B.foo 3,3 4,3 7,3 8,2 9,2 I want to get the id values of A where all the A.vals are represented by B.vals. I'm seeking a join that would return 1 and 5. 1 has vals 3,4,7 5 has vals 8,9 2 is not returned because it does not have a 7. 4 is not returned because 4,8 is not a row of B. The value of B.foo is the number of items that must match. 3,4,7 are treated a a group and consists of 3 rows. 8,9 are treated as a group and consists of 2 rows. Thanks. SELECT DISTINCT a.id FROM a JOIN b ON a.val=b.val Mar 20 '07 #2

 P: n/a gi*******************@yahoo.com wrote: Here are two tables A.id,A,val 1,3 1,4 1,7 2,3 2,4 3,8 4,8 5,8 5,9 B.val, B.foo 3,3 4,3 7,3 8,2 9,2 I want to get the id values of A where all the A.vals are represented by B.vals. I'm seeking a join that would return 1 and 5. 1 has vals 3,4,7 5 has vals 8,9 2 is not returned because it does not have a 7. 4 is not returned because 4,8 is not a row of B. SELECT a.id FROM a EXCEPT SELECT a.id FROM a WHERE a.val NOT IN ( SELECT b.val FROM b ) The value of B.foo is the number of items that must match. 3,4,7 are treated a a group and consists of 3 rows. 8,9 are treated as a group and consists of 2 rows. I'm nut sure I understand what you want to say with this. -- Knut Stolze DB2 z/OS Utilities Development IBM Germany Mar 20 '07 #3

 P: n/a I thought that this is a variation of "Relational Division". B.foo is not necessary the number of B.val of the group. It can be 'A' and 'B' instead of 3 and 2. ------------------- Commands Entered -------------------- SELECT id, val FROM A a1 , (SELECT DISTINCT foo FROM B ) b1 WHERE NOT EXISTS (SELECT * FROM B b2 WHERE b2.foo = b1.foo AND NOT EXISTS (SELECT * FROM A a2 WHERE a2.id = a1.id AND a2.val = b2.val ) ) ORDER BY id, val; ------------------------------------------------------- ID VAL ------ ------ 1 3 1 4 1 7 5 8 5 9 5 record(s) selected. If you needed only A.id, following would work. ------------------- Commands Entered -------------------- SELECT a.id FROM A INNER JOIN B b1 ON a.val = b1.val GROUP BY a.id, b1.foo HAVING COUNT(a.val) = (SELECT COUNT(b2.val) FROM B b2 WHERE b2.foo = b1.foo); --------------------------------------------------------- ID ------ 1 5 2 record(s) selected. If used a characteristics of original B table design(B.foo is the number of B.val of the group), query would be slightly simple. --------------------- Commands Entered ------------------- SELECT a.id FROM A INNER JOIN B b1 ON a.val = b1.val GROUP BY a.id, b1.foo HAVING COUNT(a.val) = b1.foo; ---------------------------------------------------------- ID ------ 1 5 2 record(s) selected. Mar 20 '07 #4

 P: n/a Thanks guys. Especially Tonkuma. I am so psyched about these tips! Beyond amazing! Mar 20 '07 #5

### This discussion thread is closed

Replies have been disabled for this discussion.