Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Red face Need complement query or intersection inverse

    Hi!

    On a previous thread I was looking for a query to find the duplicates of a table on multiple columns and returning all columns from a subset A of the table.

    Now I need to find the unique records plus the subset B of the table, so its actually the records not selected on the first query.

    From the first query I created a Table.

    So Initial Table is the Full Table, call it F
    The duplicates table is a subset of F, call it D

    I need: Not (F Intersection D) or F - D. In both records there is unique value record called RECID which has no nulls.

    I tried:

    SELECT * FROM F WHERE RECID NOT IN (SELECT RECID FROM D)

    Takes forever.

    I tried:

    SELECT F.* FROM F LEFT JOIN D ON F.RECID = D.RECID
    WHERE D.RECID IS NULL

    Executes quick but does not return the expected number of records.

    The F table has 460124 rows, The D table has 145388 rows. So I should obtain 314736 rows. The query returns 129935 rows.

    I tried:

    SELECT DISTINCT F.* FROM F, D WHERE F.RECID <> D.RECID

    Takes forever.

    Any help or idea is appreciated.

    Cordially,

    Jose

  2. #2
    Join Date
    Aug 2011
    Posts
    4

    Talking My mistake, unique key was wrong

    Looking again in the source record I noticed that the RECID is not unique.
    There is another column call ETC which is not unique either but the two columns together are unique.

    So when I tried the following query, the result set was correct:

    SELECT F.* FROM F LEFT JOIN D ON F.RECID = D.RECID AND F.ETC = D.ETC
    WHERE D.RECID IS NULL AND D.ETC IS NULL


    Cordially,

    Jose

Posting Permissions

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