Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Unanswered: How to do a join or union query while leaving out duplicates?

    I want to do a JOIN or UNION to combine 2 tables. I want all records except where field1, field2, and field3 match in both tables, I only want one record for those, doesn't matter whether it's from left or right table, but let's just say left table for simplicity's sake. I don't want it to be like this:


    field1 | field2 | field3 | field 4 | field 5 | field 6

    red three orange 54231 m 435

    red three orange 13123 f 589


    Obviously I'd only want one of the records to show up there because field1, field2, and field3 are identical. But using anything like DISTINCT won't work because they're all really distinct except those 3 columns. Thoughts?

    Code:
    SELECT field1, field2, field3, field4, field5, field6
    FROM table1
    UNION
    SELECT field1, field2, field3, field4, field5, field6
    FROM table2;
    I tried doing union and getting one giant table, then trying to clean up the duplicates using this query, but it keeps locking up access, probably because there's nearly 2 million records.

    Code:
     SELECT *
    FROM table1 AS A
    WHERE recID = (SELECT TOP 1 recID
              FROM table1 AS B
              WHERE B.field1 = A.field1 And B.field2 = A.field2 And B.field3 = A.field3
              ORDER BY  B.recID)

  2. #2
    Join Date
    Jun 2010
    Posts
    9
    Got it.

    Had to add this change.

    SELECT *
    FROM table1 AS A
    WHERE recID = (SELECT TOP 1 B.recID
    FROM table1 AS B
    WHERE B.field1 = A.field1 And B.field2 = A.field2 And B.field3 = A.field3
    ORDER BY B.recID)

Posting Permissions

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