Re: SQL- question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 07 Jul 1999 15:22:10 GMT
Message-ID: <37846d1a.7475909@inet16.us.oracle.com>

On Wed, 7 Jul 1999 15:25:48 +0200, "Ulf Grindstad" <griulf_at_online.no> wrote:

>I have to tables who is connected like this:
>
>Table A(parent)
> parent_number
>
>Table B(child)
>
>child_number
>child_zipcode
>---
>The two tables are connected with parent_number = child_number
>
>I want to find every parent that have two or more childs with the same
>zip_code.
>How do I do that?

you could try...

select child_number
  from B
 group by child_zipcode, child_number
having count(*) >= 2
/

This will give you all the child_numbers that have two or more of the same zipcodes. Given the table B with data...

SQL> select * from b;

CHILD_NUMBER CHILD_ZIPCODE
------------ -------------

           1         12345
           2         12345
           3         12345
           3         12345
           2         67890
           3         67890

SQL> l
  1 select child_number
  2 from B
  3 group by child_zipcode, child_number   4* having count(*) >= 2
SQL> / CHILD_NUMBER

---

           3

Three is the only child_number with the same zipcode 2 or more times.

Now join this with the parent table, maybe like...

SQL> l
  1 select parent_number
  2 from A
  3 where exists ( select null

  4                    from B
  5                   where parent_number = child_number
  6                   group by child_zipcode, child_number
  7*                 having count(*) >= 2 );

PARENT_NUMBER

---

            3

hope this helps.

chris.

>
>Ulf
>
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 07 1999 - 10:22:10 CDT