Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Inner Join Question

    To find non-matching rows between two tables A & B whose PK is a composite key of three cols, I use the query -

    SELECT DISTINCT A.*
    FROM Table1 A
    INNER JOIN Table2 B
    ON A.[Col1] <> B.[Col1]
    AND A.[Col2] <> B.[Col2]
    AND A.[col3] <> B.[col3]

    I need the non-matching rows of Table A only. My question is - Is the above query correct? Any better way to find non-matching rows of A.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Your query will not work.

    1)
    Select *
    from tableA ta
    where NOT EXISTS
    (select null
    from tableB tb
    where ta.a = tb.a AND
    ta.b = tb.b AND
    ta.c = tb.c)

    2)
    Select *
    from tableA ta
    LEFT OUTER JOIN
    tableB tb ON
    ta.a = tb.a AND
    ta.b = tb.b AND
    ta.c = tb.c
    WHERE tb.a IS NULL
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Thanks. I shall use option 2.

Posting Permissions

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