Fetch unique combinations of two field values

ms-access, sql

Solution

For Ms Access you can try

SELECT  DISTINCT
        *
FROM Table1 tM
WHERE NOT EXISTS(SELECT 1 FROM Table1 t WHERE tM.Source = t.Dest AND tM.Dest = t.Source AND tm.Source > t.Source)

EDIT:

Example with table Data, which is the same...

SELECT  DISTINCT
        *
FROM Data  tM
WHERE NOT EXISTS(SELECT 1 FROM Data t WHERE tM.Source = t.Dest AND tM.Dest = t.Source AND tm.Source > t.Source)

or (Nice and Access Formatted...)

SELECT DISTINCT *
FROM Data AS tM
WHERE (((Exists (SELECT 1 FROM Data t WHERE tM.Source = t.Dest AND tM.Dest = t.Source AND tm.Source > t.Source))=False));

Problem

Probably it has been asked before but I cannot find an answer. Table Data has two columns: ``` Source Dest 1 2 1 2 2 1 3 1 ``` I trying to come up with a MS Access 2003 SQL query that will return: ``` 1 2 3 1 ``` But all to no avail. Please help! UPDATE: exactly, I'm trying to exclude 2,1 because 1,2 already included. I need only unique combinations where sequence doesn't matter.

Original source