seek tips on query
Question posted by: gimme_this_gimme_that@yahoo.com
(Guest)
on
March 20th, 2007 05:35 AM
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.
4
Answers Posted
Join Bytes! wrote:
Quote:
Originally Posted by
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
Join Bytes! wrote:
Quote:
Originally Posted by
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 )
Quote:
Originally Posted by
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
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.
Thanks guys.
Especially Tonkuma.
I am so psyched about these tips!
Beyond amazing!
|
