var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Need complement query or intersection inverse
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.
SELECT * FROM F WHERE RECID NOT IN (SELECT RECID FROM D)
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.
SELECT DISTINCT F.* FROM F, D WHERE F.RECID <> D.RECID
Any help or idea is appreciated.
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