OH!
So first i would need a table of combinations and then build. But if I'm making a table of combinations then why not add the id number to the table and build a query over that?
So for each detail.id found, load a row in a table with id:function||function||function||etc.
Then just select and group the functionCat field.
embedded prepared statements would work.
Thanks for clarifying that Joe!
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta
Sent: Friday, May 29, 2009 1:01 PM
To: Midrange Systems Technical Discussion
Subject: 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
--
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.
midrange.com
