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.
Bob Stearns - 20 Mar 2007 05:44 GMT
> Here are two tables
>
[quoted text clipped - 35 lines]
>
> Thanks.
SELECT DISTINCT a.id
FROM a
JOIN b
ON a.val=b.val
Knut Stolze - 20 Mar 2007 10:28 GMT
> Here are two tables
>
[quoted text clipped - 28 lines]
> 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.

Signature
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Tonkuma - 20 Mar 2007 13:11 GMT
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.
gimme_this_gimme_that@yahoo.com - 20 Mar 2007 18:14 GMT
Thanks guys.
Especially Tonkuma.
I am so psyched about these tips!
Beyond amazing!