Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / March 2007

Tip: Looking for answers? Try searching our database.

seek tips on query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
gimme_this_gimme_that@yahoo.com - 20 Mar 2007 05:31 GMT
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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.