Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem with the query finding duplicates

    HI everybody have this table with the following fields

    locationID INT NULL,
    locsjprov nvarchar(7) null,
    addtype int null,
    address1 nvarchar (255) null

    the addtype has two values only which are 1 and 2 and some of the address1 values are duplicated.

    I want to get all of the records that follow this criteria: Give me all the records that has duplicate address1 values but has different addtype.

    WRONG result
    addtype address1
    1 sampleaddress
    1 sampleaddress

    CORRECT RESULT

    addtype address1
    1 sampleaddress
    2 sampleaddress

    with the query below it gets me all of the duplicates in address1 and not distinguish it with duplicates of address1 but different in addtype

    SELECT *
    FROM TEMP_TABLE2 AS S1
    JOIN
    (SELECT ADDRESS1
    FROM TEMP_TABLE2
    GROUP BY ADDRESS1
    HAVING (COUNT(*)>1))AS S2
    ON S1.ADDRESS1=S2.ADDRESS1

    couldn't go further with this help!!

  2. #2
    Join Date
    Dec 2007
    Posts
    11
    SELECT
    T1.*
    FROM
    TEMP_TABLE2 T1,
    TEMP_TABLE2 T2
    WHERE
    T1.addtype <> T2.addtype AND
    T1.address1=T2.address1

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    hi thanks so much that did the trick....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •