Re: Find Duplicate Data

On Wed, 3 Aug 2005 00:23:02 -0700, Travis wrote:

>Hi ,
>
> Can someone help me to build a query base on :
> SELECT Full_Name,FLT_ID, FLT_DT_ID,PNR_ID
>
> Where Full_Name,FLT_ID, FLT_DT_ID is duplicate in the same table
>
>Thank You very much

SELECT a.Full_Name, a.FLT_ID, a.FLT_FT_ID, a.PNR_ID
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Full_Name = a.Full_Name
AND b.FLT_ID = a.FLT_ID
AND b.FLT_FT_ID = a.FLT_FT_ID
AND b.PNR_ID <> a.PNR_ID

or

SELECT a.Full_Name, a.FLT_ID, a.FLT_FT_ID, a.PNR_ID
FROM MyTable AS a
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.Full_Name = a.Full_Name
AND b.FLT_ID = a.FLT_ID
AND b.FLT_FT_ID = a.FLT_FT_ID
AND b.PNR_ID <> a.PNR_ID)

or

SELECT a.Full_Name, a.FLT_ID, a.FLT_FT_ID, a.PNR_ID
FROM MyTable AS a
INNER JOIN (SELECT Full_Name, FLT_ID, FLT_FT_ID
FROM MyTable
GROUP BY Full_Name, FLT_ID, FLT_FT_ID
HAVING COUNT(*) > 1) AS b
ON b.Full_Name = a.Full_Name
AND b.FLT_ID = a.FLT_ID
AND b.FLT_FT_ID = a.FLT_FT_ID

Try them all in your database to see which one gives the best
performance.

Disclaimer: All queries above are untested, since you didn't provide
CREATE TABLE and INSERT statements to test them on.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Re: Index on two columns doesnt allow NULL in both - HELP!
    ... >I don't think Mr. Guzman's solution will work for the business problem ... not to replace the table in queries. ... Best, Hugo ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Query base on a changing table
    ... I have a query base on table. ... But every week i get a new table and i dont want to have to go in my ... bye the way i'm using Win XP with Acess 03. ... Prev by Date: ...
    (comp.databases.ms-access)