Re: Locating Primary records with identical detail records

Bill Meecham wrote:

or restated,
I just want to see those ID's that have the same FUNCTION.
in your example Detail ID=E is an orphan record if the detail is owned by the master.
still, my example would find ID's with the same FUNCTION, even the orphans.

No, here's what John wants: he wants basically to see all headers which have the same SET of detail records: if master A has three function record, M1, N4 and Z5, it would match any and every master that ALSO had exactly those three detail records.

It's a set-based operation, so it sounds SQL-ish, but in reality its not so simple, because I don't know an easy way to build a field that consists of a concatentation of the contents of a specific field for every detail record in a 1-to-many relationship.

Basically, John needs a syntactical construct in SQL that, for master A above, returns "M1N4Z5".

I tried to jigger a way using quantified predicates, but no luck. I don't think there's a way in SQL to directly compare two results sets. So even if you came up with a list of possible matches (say through a cartesian product), you couldn't somehow compare their children:

Say that POSSIBLES contains MasterID1 and MasterID2:

select * from POSSIBLES where
(select function from DETAIL: where master = MasterID1) =
(select function from DETAIL: where master = MasterID2)

I don't think there's any syntax for comparing subqueries.

Joe