Re: looking for a better way to write a complex query

From: Alan Gano <alan.gano_at_classmates.com>
Date: Thu, 04 Nov 2004 18:20:54 -0800
Message-Id: <1099621254.13901.38.camel@agano.corp.cmates.com>

An analytical solution ...

create table parent
(

   id number primary key
)
/
create table child
(

   fk             number references parent,
   field1         varchar2(10) NOT NULL,
   field2         varchar2(10) NOT NULL

)
/

insert into parent values(1);
insert into parent values(2);

insert into child values(1,'A','B');
insert into child values(1,'A','B');
insert into child values(1,'A','B');
insert into child values(2,'C','D');
insert into child values(2,'C','D');
insert into child values(2,'E','F');

commit;

select * from (

   select

      p.id,
      c.fk,
      c.field1,
      c.field2,
      count(*) over(partition by c.fk) group_count,
      count(*) over(partition by c.fk,c.field1,c.field2) same_count
   from
      parent p
      join child c on c.fk = p.id

)
where

   group_count = same_count
/

Alan.

On Thu, 2004-11-04 at 20:50 -0500, Ryan wrote:
> I'll try to explain this. Its rather tricky.
> Two Tables: Parent, Child
> Parent has a one to many relationship with Child based on the 'FK' column. Child also has two more fields lets call them Field1 and Field2. There are other fields in both tables, but they do not matter.
>
> If the child table has the same Field1 and Field2 for each FK value, then I want that record.
>
> For example, lets say we have:
>
> FK Field1 Field2
> 1 A B
> 1 A B
> 1 A B
>
> Then I need one copy of that.
>
> However, if I have the following:
>
>
> FK Field1 Field2
> 2 C D
> 2 C D
> 2 E F
>
> Then I do not want that record, because one of them is different. One way to do this is the following. I'm thinking there is a better way I can do this with an analytic function..., but I don't see it.
>
> Select fk,field1, field2
> from (
> --I only need records where there is just 1 fk, after grouping
> select fk,count(*)
> from (
> --first group by to reduce to groupings
> select fk,field1,field2,count(*)
> from child
> group by fk,field1,field2
> )
> group by fk
> having count(*) = 1
> ) a,
> child b
> -- join back to get the field1 and field2 columns
> where a.fk = b.fk
>
> There has got to be a better way than to use two group by's and a join back to the child table.
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 04 2004 - 20:18:01 CST