trying to find dup records - 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 2005 Forums

Transact-SQL (2005)

trying to find dup records

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-09-18 : 10:23:08

I am looking for the same last name 3 chars of the first and 8 of the address. This seems to bring back almost all the records

SELECT survey_rel_personalInfo.individualSurveyID AS ID, SUBSTRING(name.first, 0, 3) AS theFirstName, SUBSTRING(address.line1, 0, 8) AS theAddress,
name.last
FROM individualSurvey RIGHT OUTER JOIN
survey_rel_personalInfo ON individualSurvey.individualSurveyID = survey_rel_personalInfo.individualSurveyID LEFT OUTER JOIN
address LEFT OUTER JOIN
personalInfo ON address.addressID = personalInfo.addressID LEFT OUTER JOIN
name ON personalInfo.nameID = name.nameID ON survey_rel_personalInfo.personalInfoID = personalInfo.personalInfoID
GROUP BY survey_rel_personalInfo.individualSurveyID, SUBSTRING(name.first, 0, 3), SUBSTRING(address.line1, 0, 8), name.last

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-18 : 10:26:46

0 is not a start position for the SUBSTRING function. Since you just want the first 3 chars and first 8 chars try using LEFT(name.first, 3) and LEFT(address.line1, 8). Also, you are using outer joins, which will return results even if there is no match.

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 10:27:40

[code]SELECT survey_rel_personalInfo.individualSurveyID AS ID,
SUBSTRING(name.first, 1, 3) AS theFirstName,
SUBSTRING(address.line1, 1, 8) AS theAddress,
name.last
FROM individualSurvey
LEFT JOIN survey_rel_personalInfo ON individualSurvey.individualSurveyID = survey_rel_personalInfo.individualSurveyID
LEFT JOIN personalInfo ON survey_rel_personalInfo.personalInfoID = personalInfo.personalInfoID
LEFT JOIN address ON address.addressID = personalInfo.addressID
LEFT JOIN name ON personalInfo.nameID = name.nameID
GROUP BY survey_rel_personalInfo.individualSurveyID,
SUBSTRING(name.first, 1, 3),
SUBSTRING(address.line1, 1, 8),
name.last[/code]

E 12°55'05.25"
N 56°04'39.16"

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-09-18 : 10:34:15

Well I tried the following and got the same amount

SELECT survey_rel_personalInfo.individualSurveyID AS ID,
LEFT(name.FIRST,3) AS theFirstName,
LEFT(address.line1,8) AS theAddress,
name.LAST
FROM individualSurvey
LEFT JOIN survey_rel_personalInfo
ON individualSurvey.individualSurveyID = survey_rel_personalInfo.individualSurveyID
LEFT JOIN personalInfo
ON survey_rel_personalInfo.personalInfoID = personalInfo.personalInfoID
LEFT JOIN address
ON address.addressID = personalInfo.addressID
LEFT JOIN name
ON personalInfo.nameID = name.nameID
GROUP BY survey_rel_personalInfo.individualSurveyID,LEFT(name.FIRST,3),LEFT(address.line1,8),name.LAST

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 10:36:44

I can't tell your business logic, but try to replace some LEFT JOINs with INNER JOINs, starting at top.

E 12°55'05.25"
N 56°04'39.16"

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-09-18 : 10:40:28

Forgot one thing.....
HAVING COUNT(*) > 1

Now it seems to work

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 10:42:01

Oh, that little thing...

E 12°55'05.25"
N 56°04'39.16"

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-09-18 : 10:44:48

now I have another problem. I need to get the duplicates, Group By only brings back one. Any suggestions

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 10:52:51

If record A has 4 records in total, do you want to see all four records or only the three duplicates?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 10:57:02

[code]SELECT ID,
theFirstName,
theAddress,
theLastName
FROM (
SELECT survey_rel_personalInfo.individualSurveyID AS ID,
SUBSTRING(name.first, 1, 3) AS theFirstName,
SUBSTRING(address.line1, 1, 8) AS theAddress,
name.last AS theLastName,
SUM(1) OVER (PARTITION BY survey_rel_personalInfo.individualSurveyID) AS SumID
FROM individualSurvey
LEFT JOIN survey_rel_personalInfo ON individualSurvey.individualSurveyID = survey_rel_personalInfo.individualSurveyID
LEFT JOIN personalInfo ON survey_rel_personalInfo.personalInfoID = personalInfo.personalInfoID
LEFT JOIN address ON address.addressID = personalInfo.addressID
LEFT JOIN name ON personalInfo.nameID = name.nameID
) AS d
WHERE SumID > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-09-18 : 11:02:08

I get "The OVER SQL construct or statement is not supported."

Here is my first shot:
ALTER PROCEDURE [dbo].[prGetMultipleEntryReport]

--------------------------------------------------------------------------
--Variables passed in to the stored procedure
--------------------------------------------------------------------------
@userID int,
@sortExpression varchar(30)

AS

SET CONCAT_NULL_YIELDS_NULL OFF

DECLARE @surveyID numeric(18,0)
DECLARE @surveyTypeID int
DECLARE @lastName varchar(60)
DECLARE @firstName varchar(60)
DECLARE @latitude varchar(50)
DECLARE @longitude varchar(50)
DECLARE @SQL varchar(1000)

-----------------------------------------------
--Create Temporary Table
-----------------------------------------------
CREATE TABLE #tmpSurvey (surveyID numeric(18,0))

-----------------------------------------------
--Create Cursor
-----------------------------------------------
DECLARE csrFindMatches CURSOR
READ_ONLY

-----------------------------------------------
FOR
SELECT DISTINCT survey_rel_personalInfo.individualSurveyID AS ID, name.last AS lastName, SUBSTRING(name.first, 0,
3) AS theFirstName, SUBSTRING(CAST(personalInfo.latitude AS varchar(20)), 0, 8) AS latitude, SUBSTRING(CAST(personalInfo.longitude AS varchar(20)),
0, 8) AS longitude
FROM survey_rel_personalInfo LEFT OUTER JOIN
personalInfo ON survey_rel_personalInfo.personalInfoID = personalInfo.personalInfoID LEFT OUTER JOIN
individualSurvey ON survey_rel_personalInfo.individualSurveyID = individualSurvey.individualSurveyID LEFT OUTER JOIN
name ON name.nameID = personalInfo.nameID
WHERE (personalInfo.countyID <> 0) AND personalInfo.countyID IN (SELECT levelID
FROM userRights WHERE userID = @userID
AND accessLevel = 2) OR personalInfo.municipalityID IN
(SELECT levelID
FROM userRights WHERE userID = @userID
AND accessLevel = 2)
-----------------------------------------------

OPEN csrFindMatches

FETCH NEXT FROM csrFindMatches INTO @surveyID, @lastName, @firstName, @latitude, @longitude
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
INSERT INTO #tmpSurvey (surveyID)
SELECT survey_rel_personalInfo.individualsurveyID
FROM survey_rel_personalInfo
LEFT OUTER JOIN personalInfo ON survey_rel_personalInfo.personalInfoID = personalInfo.personalInfoID
LEFT OUTER JOIN [name] ON personalInfo.nameID = [name].nameID
WHERE (name.LAST LIKE '%' + @lastName + '%')
AND (name.FIRST LIKE '%' + @firstName + '%')
AND (CAST(personalinfo.latitude AS VARCHAR) LIKE '%' + @latitude + '%')
AND (CAST(personalinfo.longitude AS VARCHAR) LIKE '%' + @longitude + '%')
AND (personalinfo.personalinfoid <> @surveyID)
END
FETCH NEXT FROM csrFindMatches INTO @surveyID, @lastName, @firstName, @latitude, @longitude
END

-----------------------------------------------
--Free up resources
-----------------------------------------------
CLOSE csrFindMatches
DEALLOCATE csrFindMatches

------------------------------------------------------------------------
--Select data from #tmpSurvey to be returned
--and displayed in a datagrid.
------------------------------------------------------------------------

SET @SQL = 'SELECT DISTINCT #tmpSurvey.surveyID AS [ID], [name].last + '', '' + [name].first AS personName,
address.line1 + '' '' + address.line2 as personAddress,
address.city, ''('' + phone.phoneArea + '')'' + phone.phonePrefix + ''-'' + phone.phoneLine AS phone,
individualsurvey.lastUpdate AS lastUpdate
FROM #tmpSurvey
LEFT OUTER JOIN individualsurvey ON #tmpSurvey.surveyID = individualsurvey.individualsurveyID
LEFT OUTER JOIN survey_rel_personalInfo ON individualsurvey.individualsurveyID = survey_rel_personalInfo.individualsurveyID
LEFT OUTER JOIN personalInfo ON survey_rel_personalInfo.personalInfoID = personalInfo.personalInfoID
LEFT OUTER JOIN address ON personalInfo.addressID = address.addressID
LEFT OUTER JOIN phone ON personalInfo.phoneID1 = phone.phoneID
LEFT OUTER JOIN [name] ON personalInfo.nameID = [name].nameID ORDER BY ' + @sortExpression

EXEC (@SQL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 11:07:54

Why, Oh Heavenly Father why, are you allowing this man to use a CURSOR?
And where the heck did Longitude and Latitude come from?

If you are not serious about your alleged problem, I see not point in helping you further.

You might have better luck if you provide ALL information from the start, and posting accurate and proper sample data together with expected outout. It will also help us if you clearly stated your problem and what you are trying to accomplish!

E 12°55'05.25"
N 56°04'39.16"

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-09-18 : 11:13:50

I took lat long out. Just was showing what I started off with

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources