looking for a better way to write a complex query
- From: "Ryan" <ryan_gaffuri@xxxxxxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Nov 2004 20:50:57 -0500
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
Other related posts:
