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