By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,608 Members | 3,801 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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.