altarEgo
asked on
duplicate records
two tables
TEMP_CHILDQUES_PRI_DBEP_CH ILDREN has these columns
[CHPRI_SEQ], [CHPRI_CQPRI_SEQ], [CHPRI_CQID], [CHPRI_CHILD_SEQ], [CHPRI_FNAME], [CHPRI_LNAME], [CHPRI_DOB], [CHPRI_AGE], [CHPRI_PCRELATIONSHIP]
TEMP_CHILDQUES_SEC_DBEP_CH ILDREN has these columns
[CHSEC_SEQ], [CHSEC_CQSEC_SEQ], [CHSEC_CHILD_SEQ], [CHSEC_FNAME], [CHSEC_LNAME], [CHSEC_DOB], [CHSEC_AGE], [CHSEC_PCRELATIONSHIP]
select
CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
, CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
from
TEMP_CHILDQUES_PRI_DBEP_CH ILDREN, TEMP_CHILDQUES_SEC_DBEP_CH ILDREN
where CHPRI_CQPRI_SEQ = 308
and CHSEC_CQSEC_SEQ = 208
i want to do a side by side comparison of the records in those tables.
but my query is giving me duplicate records. i'm getting two copies of
each record. there are two records matching the search condition in each
of the two tables.
any ideas?
TEMP_CHILDQUES_PRI_DBEP_CH
[CHPRI_SEQ], [CHPRI_CQPRI_SEQ], [CHPRI_CQID], [CHPRI_CHILD_SEQ], [CHPRI_FNAME], [CHPRI_LNAME], [CHPRI_DOB], [CHPRI_AGE], [CHPRI_PCRELATIONSHIP]
TEMP_CHILDQUES_SEC_DBEP_CH
[CHSEC_SEQ], [CHSEC_CQSEC_SEQ], [CHSEC_CHILD_SEQ], [CHSEC_FNAME], [CHSEC_LNAME], [CHSEC_DOB], [CHSEC_AGE], [CHSEC_PCRELATIONSHIP]
select
CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
, CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
from
TEMP_CHILDQUES_PRI_DBEP_CH
where CHPRI_CQPRI_SEQ = 308
and CHSEC_CQSEC_SEQ = 208
i want to do a side by side comparison of the records in those tables.
but my query is giving me duplicate records. i'm getting two copies of
each record. there are two records matching the search condition in each
of the two tables.
any ideas?
So, there must be 2 records in one of the tables - check these out seperately -
select * from TEMP_CHILDQUES_PRI_DBEP_CH ILDREN
where CHPRI_CQPRI_SEQ = 308
and
select * from TEMP_CHILDQUES_SEC_DBEP_CH ILDREN
where CHSEC_CQSEC_SEQ = 208
whatever number records you get in these two queries, you will get the product of the two in the bigger query.
If you have duplicats, and you want to surpress duplicates, just add the DISTINCT word :
select DISTINCT
CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
, CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
from
TEMP_CHILDQUES_PRI_DBEP_CH ILDREN, TEMP_CHILDQUES_SEC_DBEP_CH ILDREN
where CHPRI_CQPRI_SEQ = 308
and CHSEC_CQSEC_SEQ = 208
select * from TEMP_CHILDQUES_PRI_DBEP_CH
where CHPRI_CQPRI_SEQ = 308
and
select * from TEMP_CHILDQUES_SEC_DBEP_CH
where CHSEC_CQSEC_SEQ = 208
whatever number records you get in these two queries, you will get the product of the two in the bigger query.
If you have duplicats, and you want to surpress duplicates, just add the DISTINCT word :
select DISTINCT
CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
, CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
from
TEMP_CHILDQUES_PRI_DBEP_CH
where CHPRI_CQPRI_SEQ = 308
and CHSEC_CQSEC_SEQ = 208
ASKER
in TEMP_CHILDQUES_PRI_DBEP_CH ILDREN i have these two rows
CHPRI_SEQ, CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
468 308 0 Ed OTTO 1/1/1989 1 son (test)
469 308 0 alice OTTO 8/25/1996 48 granddaughter (test)
in TEMP_CHILDQUES_SEC_DBEP_CH ILDREN i have these two rows
CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
204 208 0 ed OTTO 1/1/1989 1 son (test)
205 208 0 alice OTTO 8/25/1996 48 granddaughter (test)
I need to get all four rows back so I can compare them side by side. users will be checking for mismatches
between this row from the first table
468 308 0 Ed OTTO 1/1/1989 1 son (test)
and this row from the second
204 208 0 ed OTTO 1/1/1989 1 son (test)
what i get back from that query is eight rows instead of four.
CHPRI_SEQ, CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
468 308 0 Ed OTTO 1/1/1989 1 son (test)
469 308 0 alice OTTO 8/25/1996 48 granddaughter (test)
in TEMP_CHILDQUES_SEC_DBEP_CH
CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
204 208 0 ed OTTO 1/1/1989 1 son (test)
205 208 0 alice OTTO 8/25/1996 48 granddaughter (test)
I need to get all four rows back so I can compare them side by side. users will be checking for mismatches
between this row from the first table
468 308 0 Ed OTTO 1/1/1989 1 son (test)
and this row from the second
204 208 0 ed OTTO 1/1/1989 1 son (test)
what i get back from that query is eight rows instead of four.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You will need more data than just this. As Bill said, it has no way of knowing. And since this looks like genealogy type data, each of the non-key values can be the same. For example, you could legitimately have 2 Ed's, one a son and one a grandson with different birth dates. Of if you matched on birth date then you would run into trouble with twins.
What you may need to do is create 2 different selects that can be compared side by side, ordering each set of data in the same order (say birthdate then name). The it would be easy to glance down the list to see anywhere they dont line up. Fix the data, then pull it again.
With the data structure you've shown us, I cant find a way
What you may need to do is create 2 different selects that can be compared side by side, ordering each set of data in the same order (say birthdate then name). The it would be easy to glance down the list to see anywhere they dont line up. Fix the data, then pull it again.
With the data structure you've shown us, I cant find a way
No perfect but should help to remove dups and get elements side by side....
select
CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
, CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
from TEMP_CHILDQUES_PRI_DBEP_CH ILDREN
FULL OUTER JOIN TEMP_CHILDQUES_SEC_DBEP_CH ILDREN on CHPRI_FNAME = CHSEC_FNAME and CHPRI_DOB = CHSEC_DOB and CHPRI_LNAME = CHSEC_LNAME and CHPRI_CQPRI_SEQ = 308
and CHSEC_CQSEC_SEQ = 208
ORDER BY coalesce( CHPRI_FNAME,CHSEC_FNAME ), coalesce(CHPRI_LNAME, CHSEC_LNAME ), coalesce(CHPRI_DOB, CHSEC_DOB )
note : don't try to put conditions (eg CHPRI_CQPRI_SEQ = 308) in a WHERE clause, keep them in the JOIN or the OUTER join will turn into an INNER join and you'll lose rows
HTH
Hilaire
select
CHPRI_CQPRI_SEQ, CHPRI_CHILD_SEQ, CHPRI_FNAME, CHPRI_LNAME, CHPRI_DOB, CHPRI_AGE, CHPRI_PCRELATIONSHIP
, CHSEC_CQSEC_SEQ, CHSEC_CHILD_SEQ, CHSEC_FNAME, CHSEC_LNAME, CHSEC_DOB, CHSEC_AGE, CHSEC_PCRELATIONSHIP
from TEMP_CHILDQUES_PRI_DBEP_CH
FULL OUTER JOIN TEMP_CHILDQUES_SEC_DBEP_CH
and CHSEC_CQSEC_SEQ = 208
ORDER BY coalesce( CHPRI_FNAME,CHSEC_FNAME ), coalesce(CHPRI_LNAME, CHSEC_LNAME ), coalesce(CHPRI_DOB, CHSEC_DOB )
note : don't try to put conditions (eg CHPRI_CQPRI_SEQ = 308) in a WHERE clause, keep them in the JOIN or the OUTER join will turn into an INNER join and you'll lose rows
HTH
Hilaire
ASKER
thanks bill and all who offered suggestions. i ended up using two separate selects in two stored procedures to do the side by side.
cheers
cheers
ASKER