dupications in a 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

General SQL Server Forums

New to SQL Server Programming

dupications in a query

Author

Topic

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 07:47:03

i am trying to run a query but when i run what i have done duplicates keeps coming up when there isnt in the database. anyone any ideas why. here is the code


SELECT  tc.CLFirstName,
		tc.CLSurname,
		tc.CLNotes,
		tc.CLDOB
FROM Maes_harvest.dbo.tblClients tc
INNER
JOIN Maes_harvest.dbo.TblPolaris tp
ON tc.CLID = tp.IndividualNo
INNER
JOIN Maes_harvest.dbo.tblCommunicationInformation  tcm
ON  tc.CLID = tcm.CIConID
where tc.CLDOB >= 1950

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 07:54:40

The duplicate records are due to the JOINS.
If there are only one record in tc table, and two records in tp table with same CLID/IndividualNo, the result is two records.

E 12°55'05.63"
N 56°04'39.26"

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 07:56:57

if i run it like this
SELECT *
FROM Maes_harvest.dbo.tblClients tc
INNER
JOIN Maes_harvest.dbo.TblPolaris tp
ON tc.CLID = tp.IndividualNo
INNER
JOIN Maes_harvest.dbo.tblCommunicationInformation tcm
ON tc.CLID = tcm.CIConID

it only comes up once

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 08:00:29

So what you are saying is that adding a WHERE clause is making your query duplicating the records?

where		tc.CLDOB >= 1950

E 12°55'05.63"
N 56°04'39.26"

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 08:15:30

need to find the people 59 plus so i need it

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 08:16:06

i just didnt copy and paste it all in

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-15 : 08:24:16

You'd see duplicates from your second query also if you see it in your first. Check again.

As for why you are getting duplicates, check if you have duplicates in tblClients basis CLID, TblPolaris basis IndividualNo or tblCommunicationInformation basis CIConID.

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 08:26:54

ok will look again thanks or all the help

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 08:32:59

actually is there a way i can just get one to show up and not them all

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-15 : 08:37:54

Offcourse there is. But your question was related to why there are duplicates, not how you can just display the distinct ones.

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 08:42:55

ok thanks again. i will use distinct

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 09:06:54

ok sorry again but i used distinct
SELECT DISTINCT
tc.CLFirstName,
tc.CLSurname,
--tc.CLNotes,
CONVERT(nvarchar, CLNotes),
tc.CLDOB,
tcm.CIInfo
FROM Maes_harvest.dbo.tblClients tc
INNER
JOIN Maes_harvest.dbo.TblPolaris tp
ON tc.CLID = tp.IndividualNo
INNER
JOIN Maes_harvest.dbo.tblCommunicationInformation tcm
ON tc.CLID = tcm.CIConID
where tc.CLDOB >= 1950

but its still pickin up duplicates. anyone any ideas

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-15 : 09:10:34

I repeat,, check if you have duplicates in tblClients basis CLID, TblPolaris basis IndividualNo or tblCommunicationInformation basis CIConID.

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 09:17:04

there is no duplicates in the client table. duplicates in the other 2

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-15 : 09:24:49

Now, you might have different values of CIInfo for the same CIConID in tblCommunicationInformation table. Which one would you want to pick??
Why don't you post a few duplicates from both tables and then post the criteria to pick one from them, that'd make it clear.

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 09:28:02

ya that must be it.i will have to look into it a bit more

rjhe25
Starting Member

21 Posts

Posted - 2009-04-15 : 11:14:29

ok i have it now here is the code im using
SELECT DISTINCT
tc.CLFirstName,
tc.CLSurname,
CONVERT(nvarchar, CLNotes),
tc.CLDOB,
DATEDIFF(year, CLDOB, GETDATE())as Age,
(SELECT TOP 1 tcm.CIInfo
FROM dbo.tblCommunicationInformation tcm
WHERE tcm.CIConID = tp.IndividualNo
AND tcm.CICommID = 30)
AS EMail ,
(SELECT TOP 1 tcm.CIInfo
FROM dbo.tblCommunicationInformation tcm
WHERE tcm.CIConID = tp.IndividualNo
AND tcm.CICommID = 734)
AS Address ,
(SELECT TOP 1 tl.LIName
FROM dbo.tblLists tl
WHERE tc. CLConsultantID = tl.LIID
)
AS Consultant
FROM Maes_harvest.dbo.tblClients tc
INNER
JOIN Maes_harvest.dbo.TblPolaris tp
ON tc.CLID = tp.IndividualNo

where CLDOB < 1950.

ok im havin one problem with the bod. i was asked to get the query to work for people 59+
if i put the sign they way i have it it picks up nothing.
if i put the sign like this > it pick up people over and under the ages im looking for. anyone any ideas what the best way would be for getting the people 59 and over

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:34:26

you may be better off explaining your reqmnt rather than giving query. also remeber to give sample data in below format
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources