Re: looking for a better way to write a complex query
- From: Alan Gano <alan.gano@xxxxxxxxxxxxxx>
- To: ryan_gaffuri@xxxxxxxxxxx
- Date: Thu, 04 Nov 2004 18:20:54 -0800
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.
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
