Duplicate Records on query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicate Records on query.

Author

Topic

POnfri
Starting Member

3 Posts

Posted - 2004-02-23 : 22:15:10

Ok hopfully i can explain this in a way i don't confuse you folks. Here is the setting:

Two Tables (MS SQL)
1) Treatment
2) TreatmentInvoice

Records: 3 total
1 2/23/2004 Cash
1 2/23/2004 Cash
2 2/23/2004 Check

NOTE: In both of these tables theres a field called CustomersID and there can be multiple records with the same ID since a customer can get multiple treatments.

The query (query analizer)

SELECT Treatment.TreatmentDate, TreatmentInvoice.PaymentType
FROM Treatment JOIN TreatmentInvoice
ON Treatment.CustomersID = TreatmentInvoice.CustomersID
WHERE Treatment.TreatmentDate = '2/23/2004'

The PROBLEM

I get duplicate records for every customer that has more then one treatment. In the example above i get 1 Check and 4 Cash

TreatmentDate PaymentType
------------- ----------------
2/23/2004 Cash
2/23/2004 Check
2/23/2004 Check
2/23/2004 Check
2/23/2004 Check

(5 row(s) affected)

Please help me understand why its doing this. I have also tryed it with JOIN and still get the same result????

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-24 : 00:19:03

You haven't posted the structure of the TreatmentInvoice table, but assuming you have a TreatmentID in that table as well, you will need to add it to the join condition.


SELECT Treatment.TreatmentDate, TreatmentInvoice.PaymentType
 FROM Treatment INNER JOIN TreatmentInvoice
 ON Treatment.CustomersID = TreatmentInvoice.CustomersID
 AND Treatment.TreatmentID = TreatmentInvoice.TreatmentID
 WHERE Treatment.TreatmentDate = '2/23/2004'

If you don't do this, you will get duplicates because your join information would be incomplete. Also, do you have a good reason for storing CustomerID in both the Treatment and TreatmentInvoice tables? If they are always going to be the same value for a given Treatment, you will only be duplicating data and violating the rules of normalization. You might want to re-consider the need to have CustomerID in the TreatmentInvoice table.

OS

POnfri
Starting Member

3 Posts

Posted - 2004-02-24 : 09:49:06

Thank you for the help. The reson i have a customerID is because one customer can have multiple treatments. So in the case were i want to run a query say get the customer name and all treatments this customer has taken, i can do this using the customerID. As for the treatment ID i do not have a treatment ID field, do i really need to add this as well??

Thanks a bunch.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-25 : 00:47:57

Can you post the DDL i.e. the structure of both the tables?

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources