Prev: Stored Proc Question: Use ad hoc Where Clause
Next: Open a Windows Form from SSRS
From: Jay via SQLMonster.com on 25 Jun 2010 17:48
I need to elimnate duplicates the following script. I believe what is
creating the duplicates is the provcont table, there are different provcont
ids for each record. Thanks in advance.

SELECT PROVCONT.C_FAX,
PROVIDER.NAME,
PROVIDER.PROV_PATH,
PROVIDER.PROV_ID,
PROVSTAT.STATUS,
PSTATUS.DESCRIPT,
PROVCONT.CONTAC_ID

FROM PROVCONT

INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID
INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID
INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS

WHERE (PROVIDER.PROV_PATH = 2) AND (PROVSTAT.STATUS = 3) AND PROVCONT.
CONTAC_ID

--
Message posted via http://www.sqlmonster.com

From: Erland Sommarskog on 25 Jun 2010 18:29
Jay via SQLMonster.com (u7124(a)uwe) writes:
> I need to elimnate duplicates the following script. I believe what is
> creating the duplicates is the provcont table, there are different
> provcont ids for each record. Thanks in advance.
>
> SELECT PROVCONT.C_FAX,
> PROVIDER.NAME,
> PROVIDER.PROV_PATH,
> PROVIDER.PROV_ID,
> PROVSTAT.STATUS,
> PSTATUS.DESCRIPT,
> PROVCONT.CONTAC_ID
>
> FROM PROVCONT
>
> INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID
> INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID
> INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS
>
> WHERE (PROVIDER.PROV_PATH = 2) AND (PROVSTAT.STATUS = 3) AND PROVCONT.
> CONTAC_ID

You can always take the easy route out and add DISTINCT to the SELECT
clause.

But often duplicates in the output indicates that you have not thought
the whole way through of what you are looking for. Since I don't know
your tables, I can't say what might be wrong here - or whether anything
is wrong at all.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: ray on 26 Jun 2010 14:52
Jay and Erland,
I've successfully used the following 'very short' update routine to isolate
and then remove duplicate records.

--Used to de-dup records

--Table must have (a) Rec_ID column Identity(1,1), AND (b) DUP column
Char(1)

--THis procedure de-dups records that contain identical Matchkey (dupcolumn)

--records within the table itself.

To implement the following you need to:

A) Determine which column has the duplicates that you need to de-dup

B) Have a unique RecordID column of type INT - use Identity(1,1)

C) Alter the table and add a DUP column of type Char(1)

D) You can then inspect the table to see that the column Dup marked with a
'Y'

is an actual duplicate record based on your dupcolumn.

E) You can then delete all records that have DUP='Y'



UPDATE table_name SET DUP = ''Y''

FROM

table_Name

WHERE EXISTS

(SELECT NULL FROM table_name B WHERE B.dupcolumn = table_name.dupcolumn

GROUP BY B.dupcolumn

HAVING table_Name.rec_ID <MAX(B.rec_ID))


Enjoy

-Ray


"Jay via SQLMonster.com" <u7124(a)uwe> wrote in message
news:aa12a587a82d4(a)uwe...
>I need to elimnate duplicates the following script. I believe what is
> creating the duplicates is the provcont table, there are different
> provcont
> ids for each record. Thanks in advance.
>
> SELECT PROVCONT.C_FAX,
> PROVIDER.NAME,
> PROVIDER.PROV_PATH,
> PROVIDER.PROV_ID,
> PROVSTAT.STATUS,
> PSTATUS.DESCRIPT,
> PROVCONT.CONTAC_ID
>
> FROM PROVCONT
>
> INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID
> INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID
> INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS
>
> WHERE (PROVIDER.PROV_PATH = 2) AND (PROVSTAT.STATUS = 3) AND PROVCONT.
> CONTAC_ID
>
> --
> Message posted via http://www.sqlmonster.com
>


 | 
Pages: 1
Prev: Stored Proc Question: Use ad hoc Where Clause
Next: Open a Windows Form from SSRS