CODEWITHSUNDEEP
sqlvbams-access
daZza
daZza

Reputation: 1689

How to INTERSECT in MS Access?

Somehow MS Access doesn't seem to have access to the keyword INTERSECT, which I need right now in order to fix some data errors in my database.

I had a look on older questions and found some answers that tried to solve this problem by using a select distinct [...] inner join [...] construction.

However, this doesn't seem to help me, as the syntax doesn't apply to the table I need to intersect.

Here's a short explanation on what I want to achieve with the INTERSECT:

My table looks like this (simplified):

A | B

hello | world

world | hello

Now these datasets are redundant, but unfortunately can't be caught by a constraint, as they are not exactly the same, but mirrored. That's why they got inserted in the first place...

I think normally I would be able to select all the affected data sets by using a 

 SELECT A,B FROM tbl
 INTERSECT
 SELECT B,A from tbl

After having selected them I could just delete them and would be rid of that problem... Does anyone have an idea on how to implement this using MS Access?

Upvotes: 1

Views: 13243

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

For test data in a table named [MirrorTest]

pk  A      B    
--  -----  -----
 1  foo    bar  
 2  hello  world
 3  hello  there
 4  world  hello
 5  bar    baz  
 6  bar    foo

the query

    SELECT pk, A, B
    FROM MirrorTest
    WHERE A<=B
UNION ALL
    SELECT pk, B, A
    FROM MirrorTest
    WHERE A>B

will return all of the rows such that A<=B, i.e.,

pk  A      B    
--  -----  -----
 2  hello  world
 3  hello  there
 5  bar    baz  
 6  bar    foo  
 1  bar    foo  
 4  hello  world

Wrap that in an aggregation query to find the candidates for deletion, defined as the larger [pk] value where there are duplicates

SELECT Max(pk) AS pkToDelete
FROM
    (
        SELECT pk, A, B
        FROM MirrorTest
        WHERE A<=B
    UNION ALL
        SELECT pk, B, A
        FROM MirrorTest
        WHERE A>B
    ) AS u
GROUP BY A, B
HAVING COUNT(*) > 1

returns

pkToDelete
----------
         6
         4

so you could just use that in the WHERE clause of a DELETE query

DELETE FROM MirrorTest
WHERE pk IN
    (
        SELECT Max(pk) AS pkToDelete
        FROM
            (
                SELECT pk, A, B
                FROM MirrorTest
                WHERE A<=B
            UNION ALL
                SELECT pk, B, A
                FROM MirrorTest
                WHERE A>B
            ) AS u
        GROUP BY A, B
        HAVING COUNT(*) > 1
    )

Upvotes: 4

Related Questions