looking for a better way to write a complex query

From: Ryan <ryan_gaffuri_at_comcast.net>
Date: Thu, 4 Nov 2004 20:50:57 -0500
Message-ID: <011901c4c2d9$e5680db0$350a5444@ryannew>

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 Received on Thu Nov 04 2004 - 19:46:33 CST