how to create sql code not duplicate Record

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • cehrfaee
    New Member
    • Jun 2012
    • 1

    how to create sql code not duplicate Record

    Hi!
    Please help me!
    How to create sql code ..
    Records from two tables do not match
    Thank You!
    Table 1
    Per1 Per2 Per3
    A1 B1 C1
    A1 B1 C2
    A1 B1 C3
    A1 B1 C4
    A2 B1 C1
    A2 B1 C2
    A2 B1 C3
    A2 B2 C4
    A2 B2 C1
    A2 B2 C2
    A2 B2 C3

    Table 2
    Per1 Per2 Per3
    A1 B1 C3
    A2 B1 C2
    A2 B2 C1

    Results
    Per1 Per2 Per3
    A1 B1 C1
    A1 B1 C2
    A1 B1 C4
    A2 B1 C1
    A2 B1 C3
    A2 B2 C4
    A2 B2 C2
    A2 B2 C3
  • Mihail
    Contributor
    • Apr 2011
    • 759

    #2
    This work on your test data but do more tests to be sure.

    Code:
    SELECT DISTINCT Table1.Per1, Table1.Per2, Table1.Per3
    FROM Table1 LEFT JOIN Table2 ON (Table1.Per3 = Table2.Per3) AND (Table1.Per2 = Table2.Per2) AND (Table1.Per1 = Table2.Per1)
    WHERE (((([Table1]![Per1]=[Table2]![Per1]) And ([Table1]![Per2]=[Table2]![Per2]) And ([Table1]![Per3]=[Table2]![Per3])) Is Null))
    ORDER BY Table1.Per1, Table1.Per2, Table1.Per3;

    Comment

    Working...