Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Union qry to find missing items

    I have 2 tables, 1 is the result of a qry from months ago the 2nd is results of a current qry. I would like to find records in table1 that are not in table 2, and records in table 2 that are not in table 1.


    i found an example :

    SELECT Table1.Field1, Table1.Field2, Table1.Field3
    FROM Table1 LEFT JOIN Table2 ON Table1.Field1= Table2.Field1
    WHERE (((table2.Field1) Is Null))
    UNION
    SELECT Table2.Field1, Table2.Field2, Table2.Field3
    FROM Table2 LEFT JOIN Table1 ON Table1.Field1= Table2.Field1
    WHERE (((table1.Field1) Is Null))

    - but i am concerned that this wont do the trick -since there are lots of $ amounts and dates that will match and other colms also.

    would i need a union qry like this and have to left join on EVERY COLUMN and have every column in the where clause?

    Tnks

    M~

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    If you are running Oracle, you might consider using the MINUS operator

    Example:

    select column_1, column_2, column_3, ...<include all columns>
    from table1
    MINUS
    select column_1, column_2, column_3, ...<include all columns>
    from table2

    will return all rows in table1 that do not have a match in table2.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The $'s wont be a problem, you will only have to join/where on a candidate key for each table.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    "I would like to find records in table1 that are not in table 2, and records in table 2 that are not in table 1."

    (select name, dept
    from table1
    minus
    select name, dept
    from table2)
    UNION
    (select name, dept
    from table2
    minus
    select name, dept
    from table1);

    I personally prefer the first query although I initially just did a left outer join, because it seems easier to follow and smaller to write.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    alternatively if you like UNION, INTERSECT and MINUS you can use all three of them

    (select *
    from table1
    UNION
    select *
    from table2)
    MINUS
    (select *
    from table1
    INTERSECT
    select *
    from table2);

    I do like the outer join the best as it most probably would be faster, however these are in my opinion slower alternatives.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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