Re: Locating Primary records with identical detail records

Charles Wilt wrote:

I was thinking that basically, what you are looking for is equivalent sets....

Seems like that should have been done before, google turned up this:
http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1224863_mem1,00.html


Looks like there's a handful of ways to do this.

Eh, to some degree. I didn't read the whole article because I have no particular desire to subscribe to an Oracle newsletter, but the first option was a FULL OUTER JOIN. FOJ is expensive enough, but then you still have to do that on every possible combination of master IDs. The processing rises exponentially with the number of masters, and in addition it rises arithmetically with the number of details.

Viable perhaps for files with thousands of records, not so much for millions.

However, you could relatively quickly reduce the number of possibilities with a CTE:

WITH possibles AS
(SELECT DISTINCT a.master AS master1, b.master AS master2
FROM detail a, detail b WHERE a.function = b.function)

That gives you POSSIBLES as the list of pairs of masters with at least one function in common.

Another comparison would be comparing all masters with an equal number of children. That also gives you a trimmed down list of possibles.

Then just do exception joins each way and include only those with no exceptions.

Joe