Re: Locating Primary records with identical detail records

My example was flawed. There will never be records in detail without a related
record in master. Never is an EXTREMELY dangerous word, I know. But, that is
not SUPPOSED to happen <g>.

There are 2723 records in Master, and 18720 in detail.

The function field is ten characters long. I currently do not know the largest
number of detail records associated with any given master record.

I meant to reply sooner, but our connection to the outside world was down three
hours, and then I was in a meeting for another hour.

Thanks so much for the suggestions. And, for the time you gave to this problem.
I had wondered if there was an "easier" way to do this than brute force. I had
hopes that SQL could help, and that I could use the issue as another opportunity
to explore the power of SQL. This is not an earth-shattering issue. Just
something that could help me do my job a little better.


John McKee

Quoting Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>:

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.