Keep only unmatched records when joining tables-CodePudding

I have 2 access tables that contain partially similar data, one being more enriched than the other. The idea here is to join the two tables by the fields id and num and to get from the table T2 the num that are not in the table T1

T1:

id	num
1	34
3	51
7	23

T2:

id	num	status
1	34	done
1	79	done
1	39	done
3	51	done
7	23	done

Expected result:

id	num	status
1	79	done
1	39	done

Under access I read on internet that there is no MINUS operator like under MySQL, so I tried with EXCEPT but the query takes a long time (stopped after 10min)

So I tried this:

SELECT T2.*
FROM T2 LEFT JOIN T1 ON (T1.id =T2.id) 
WHERE T1.num IS NULL AND ( (T2.status LIKE 'done') );

The problem now is, I don't have all the records that are in T2 but not in T1

CodePudding user response：

You can use RIGHT JOIN. And I recommend to do not use "LIKE" in this case because this is slow. You can just use the = operator. So your query will be:

SELECT t2.id, t2.num, t2.status 
FROM t1 
RIGHT JOIN t2 
ON t1.id = t2.id
AND t1.num = t2.num
WHERE t1.num IS NULL
AND t2.status = 'done';

In case all column names you want to join are identic in both tables, you can join more simple:

SELECT t2.id, t2.num, t2.status 
FROM t1 
RIGHT JOIN t2 
USING (id,num)
WHERE t1.num IS NULL
AND t2.status = 'done';

I don't like this, but it's shorter. At the end, your concrete query depends on your personal "taste".

CodePudding user response：

You're missing a condition on the join:

SELECT T2.*
FROM      T2 
LEFT JOIN T1 
       ON T1.id  = T2.id
      AND T1.num = T2.num
WHERE T1.num IS NULL 
  AND T2.status LIKE 'done';

Check it here.

CodePudding user response：

There is a lot of variants.

SELECT t2.*
FROM t2
LEFT JOIN t1 USING (id, num)
WHERE t1.id IS NULL
  AND t2.status = 'done'

SELECT *
FROM t2
WHERE NOT EXISTS ( SELECT NULL
                   FROM t1
                   WHERE (t1.id, t1.num) = (t2.id, t2.num) )
  AND status = 'done'

There are more variants...

What of these (or maybe some another) variants is the most effective? this depends on the tables definitions and data statistic.