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

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