RE: Locating Primary records with identical detail records

John sez:
"I just want to see those entries in Master that have the same detail records."



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.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of John McKee
Sent: Friday, May 29, 2009 11:55 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Locating Primary records with identical detail records


Maybe this will clear it up:

Master ID
A
C
D
L
M
N

Detail Id, Function
C AA
D BC
E AA
L BB
L CC
L FF
L ZZ
M CC
N BB
N CC
N FF
N ZZ


'A' has no records in the Detail table
"L" and "N" has the same functions in the detail table

I just want to see those entries in Master that have the same detail records.

John McKee

Quoting rob@xxxxxxxxx:

I'm with Charles on this one. We're confused. Are you saying that
FUNCTIONS may be the same (for example 'X') in ID 105 and ID 217 but they
may still not be the same because you then have to evaluate other columns
in the rows to see if they are similar or different? How many columns are
in the table? What columns can be different versus those that must be the
same (as to define duplicate)?

Let's say that ID can be different. FUNCTIONS must be the same. Columns
A, B, C must be the same. Columns D, E and F could be different. You
could do something like

Select M.*, D.*
from master m left outer join detail d on (m.id=d.id)
where d.functions, d.a, d.b, d.c in (
select e.functions, e.a, e.b, e.c
from detail e
group by e.functions, e.a, e.b, e.c
having count(*)>1)
order by d.functions, d.a, d.b, d.c, d.id


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
John McKee <jmmckee@xxxxxxxxxxxxxx>
To:
midrange-l@xxxxxxxxxxxx
Date:
05/29/2009 10:43 AM
Subject:
Re: Locating Primary records with identical detail records
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



An earlier effort, in RPG III, had to work with the limit of an array.
Been
years since I attempted this. Point was that I could not fit all the
Detail
record FUNCTIONS into a record for sorting. Likely, the size limit has
been
eased over the years. But, a record length of 10000 just to accommodate
all
possibilities seemed a bit excessive.

John McKee

Quoting Charles Wilt <charles.wilt@xxxxxxxxx>:

I'm confused...you say "There are several instances where the Detail
records for one Master record are the same as
the Detail records for another Master record."

Then you say, "Still was a manual process to examine how closely the
records matched."

How could they be the same but not match?

Charles

On Fri, May 29, 2009 at 10:06 AM, John McKee <jmmckee@xxxxxxxxxxxxxx>

wrote:

I have two files, Master and Detail. They share a common key, ID.
There can be
any number of Detail records, even zero, for every Master record.
The Detail
records have a secondary key sequence, FUNCTION, in ascending order.
There are
several instances where the Detail records for one Master record are
the same as
the Detail records for another Master record.

In the past, I have tried to locate these by using RPG and building big
variables containing the FUNCTION, then sorting them. Still was a

manual

process to examine how closely the records matched.

Can SQL do this?

John McKee

--
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.

--
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.

--
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.



--
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.

--
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.