?
Solved

duplicate records

Posted on 2004-09-01
7
Medium Priority
?
227 Views
Last Modified: 2012-06-27
two tables
TEMP_CHILDQUES_PRI_DBEP_CHILDREN 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_CHILDREN 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_CHILDREN, TEMP_CHILDQUES_SEC_DBEP_CHILDREN
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?

0
Comment
Question by:altarEgo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Author Comment

by:altarEgo
ID: 11958027
i misspoke.  not duplicate records, but duplicate results.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11958254
So, there must be 2 records in one of the tables - check these out seperately -

select * from TEMP_CHILDQUES_PRI_DBEP_CHILDREN
where CHPRI_CQPRI_SEQ = 308

and

select * from TEMP_CHILDQUES_SEC_DBEP_CHILDREN
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_CHILDREN, TEMP_CHILDQUES_SEC_DBEP_CHILDREN
where CHPRI_CQPRI_SEQ = 308
and CHSEC_CQSEC_SEQ = 208
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11958405
in TEMP_CHILDQUES_PRI_DBEP_CHILDREN 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_CHILDREN 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.

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 17

Accepted Solution

by:
BillAn1 earned 375 total points
ID: 11958497
trouble is, if there is 2 records in each half, if you join them you will get 4, because each of the 2 in each half will be paired off with both of the records in the 2nd half. If you only want row1 of the 1st to match row1 of the second etc, you need some way to 'link' them - is there a rule that says any of the cols will be guaranteed to match, or will there be the possibility that all cols are different?

If there is no hard and fast rule to link which goes with which, you will struggle to link them. Even by order, there is no guarantee that the rows will come out in corresponding order (unless there is a 'rule' - e.g. order by the first name? ( but that would foul up if 'ed' was accidently entered as 'ad' in one of the rows - they would be crossed then!!

If you can come up with a good business rule for how to link the records, it can be done, otherwise, the users will have to visually try to match them afterwards.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 11959118
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

0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11961421
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_CHILDREN
FULL OUTER JOIN TEMP_CHILDQUES_SEC_DBEP_CHILDREN 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
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11963929
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question