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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Duplicate results
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

domleg101
Starting Member

2 Posts

Posted - 03/22/2007 :  16:10:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 03/22/2007 :  16:28:33  Show Profile  Reply with Quote
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


Edited by - snSQL on 03/22/2007 16:29:49
Go to Top of Page

domleg101
Starting Member

2 Posts

Posted - 03/23/2007 :  09:10:27  Show Profile  Reply with Quote
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
Go to Top of Page

joblenis
Starting Member

Canada
29 Posts

Posted - 03/23/2007 :  11:10:31  Show Profile  Reply with Quote
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]
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 03/23/2007 :  12:04:48  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 03/23/2007 :  14:12:09  Show Profile  Reply with Quote
"I have a table that has more than one calving date per cow "Would these be Cows?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000