Duplicate results - 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

General SQL Server Forums

New to SQL Server Programming

Duplicate results

Author

Topic

domleg101
Starting Member

2 Posts

Posted - 2007-03-22 : 16:10:30

Hello,

I'm new to SQL and tried to develop a simple query on multiple tables of a database. The query works but returns duplicate records even though there aren't duplicate records in the database.

Here is the script:

SELECT LelyDevice.iAddress, LelyDeviceVisit.tEndVisitDateTime, LelyCow.dBirthDate, LelyCow.iUserNumber, LelyAstronautVisit.lRefusal,
LelyAstronautVisit.lFailedMilking, LelyMilkVisitData.nMilkYield, LelyLactationProduction.dCalvingDate
FROM LelyDevice INNER JOIN LelyDeviceVisit ON LelyDevice.PK_LelyDevice = LelyDeviceVisit.FK_LelyDevice
INNER JOIN LelyCow ON LelyDeviceVisit.FK_LelyCow = LelyCow.PK_LelyCow
INNER JOIN LelyAstronautVisit ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyAstronautVisit.FK_LelyDeviceVisit
INNER JOIN LelyMilkVisitData ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyMilkVisitData.FK_LelyDeviceVisit
INNER JOIN LelyLactationProduction ON LelyCow.PK_LelyCow = LelyLactationProduction.FK_LelyCow

WHERE LelyDeviceVisit.tEndVisitDateTime BETWEEN '2006-07-29 03:20:00.000' AND '2006-08-03 08:58:59.000' AND LelyAstronautVisit.lFailedMilking = 1
ORDER BY LelyDeviceVisit.tEndVisitDateTime DESC

and here is the result:
1301 2006-08-03 08:57:39.000 2002-07-03 00:00:00.000 209 0 1 5.1 2005-06-02 00:00:00.000
1301 2006-08-03 08:57:39.000 2002-07-03 00:00:00.000 209 0 1 5.1 2004-06-06 00:00:00.000
1301 2006-08-02 20:21:54.000 2002-07-03 00:00:00.000 209 0 1 2.7 2005-06-02 00:00:00.000
1301 2006-08-02 20:21:54.000 2002-07-03 00:00:00.000 209 0 1 2.7 2004-06-06 00:00:00.000
1101 2006-08-02 19:26:44.000 2002-11-20 00:00:00.000 221 0 1 0.0 2005-03-27 00:00:00.000
1301 2006-08-02 19:16:33.000 2002-11-20 00:00:00.000 221 0 1 2.3 2005-03-27 00:00:00.000

As you can see the first 2 lines are the same date and time and the next 2 and so on.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-22 : 16:28:33

Basically what is happening is that one row in one of the tables is being joined to two rows in another table. The two rows that are being joined are probably unique, but you aren't including the column/s that make them unique in the query (I don't know for sure which tables are causing it because you didn't give input data).

You need to either add those column/s to the query (if you do in fact need them) or add the DISTINCT keyword to exclude the duplicates.

For example:

create table #Customers
(
CustomerID int,
CustomerName varchar(20)
)
create table #Orders
(
OrderID int,
CustomerID int,
OrderDate datetime
)

insert #Customers
select 1, 'Mickey Mouse' union all
select 2, 'Donald Duck' union all
select 3, 'Goofy'
insert #Orders
select 1, 1, '1/1/2007' union all
select 2, 1, '1/1/2007' union all
select 3, 2, '1/2/2007' union all
select 4, 2, '1/2/2007' union all
select 5, 3, '1/3/2007' union all
select 6, 3, '1/4/2007'

--Query with duplicates
select C.CustomerName, O.OrderDate
from #Customers C 
inner join #Orders O on C.CustomerID = O.CustomerID

--Query 1 without duplicates
select C.CustomerName, O.OrderID, O.OrderDate
from #Customers C 
inner join #Orders O on C.CustomerID = O.CustomerID

--Query 2 without duplicates
select distinct C.CustomerName, O.OrderDate
from #Customers C 
inner join #Orders O on C.CustomerID = O.CustomerID

drop table #Customers
drop table #Orders

domleg101
Starting Member

2 Posts

Posted - 2007-03-23 : 09:10:27

Here's the problem, I have a table that has more than one calving date per cow and I want to only get the most recent one for each cow when I join that table with the others in my query. The table is dbo.LelyLactationProduction

joblenis
Starting Member

29 Posts

Posted - 2007-03-23 : 11:10:31

I dont see any duplications

and here is the result:
1301 2006-08-03 08:57:39.000 2002-07-03 00:00:00.000 209 0 1 5.1 2005-06-02 00:00:00.000
1301 2006-08-03 08:57:39.000 2002-07-03 00:00:00.000 209 0 1 5.1 2004-06-06 00:00:00.000
1301 2006-08-02 20:21:54.000 2002-07-03 00:00:00.000 209 0 1 2.7 2005-06-02 00:00:00.000
1301 2006-08-02 20:21:54.000 2002-07-03 00:00:00.000 209 0 1 2.7 2004-06-06 00:00:00.000
1101 2006-08-02 19:26:44.000 2002-11-20 00:00:00.000 221 0 1 0.0 2005-03-27 00:00:00.000
1301 2006-08-02 19:16:33.000 2002-11-20 00:00:00.000 221 0 1 2.3 2005-03-27 00:00:00.000

As you can see the first 2 lines are the same date and time and the next 2 and so on.
[/quote]

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-23 : 12:04:48

This should give you what you're looking for

SELECT LelyDevice.iAddress, LelyDeviceVisit.tEndVisitDateTime, LelyCow.dBirthDate, LelyCow.iUserNumber, LelyAstronautVisit.lRefusal,
LelyAstronautVisit.lFailedMilking, LelyMilkVisitData.nMilkYield, LLP.dCalvingDate
FROM LelyDevice INNER JOIN LelyDeviceVisit ON LelyDevice.PK_LelyDevice = LelyDeviceVisit.FK_LelyDevice
INNER JOIN LelyCow ON LelyDeviceVisit.FK_LelyCow = LelyCow.PK_LelyCow
INNER JOIN LelyAstronautVisit ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyAstronautVisit.FK_LelyDeviceVisit
INNER JOIN LelyMilkVisitData ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyMilkVisitData.FK_LelyDeviceVisit
INNER JOIN (SELECT FK_LelyCow, MAX(dCalvingDate) AS dCalvingDate
FROM LelyLactationProduction GROUP BY FK_LelyCow) AS LLP
ON LelyCow.PK_LelyCow = LLP.FK_LelyCow

WHERE LelyDeviceVisit.tEndVisitDateTime BETWEEN '2006-07-29 03:20:00.000' AND '2006-08-03 08:58:59.000' AND LelyAstronautVisit.lFailedMilking = 1
ORDER BY LelyDeviceVisit.tEndVisitDateTime DESC

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-23 : 14:12:09

"I have a table that has more than one calving date per cow "Would these be

Cows?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources