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

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

Other related posts: