General discussion

Locked

SQL "inverse duplicates" query?

By Mr. Origami ·
Anyone have any ideas for a "find inverse duplicates" query in Access? Here's what I mean. I have one table - tblColors. Two fields - color1 and color2. Table tracks colors that look good together. So, someone may have:

Color1........Color2
----------------------
red...........orange
blue..........green
orange........red

How do I weed out the duplicate red/orange pair? It's not really a duplicate record, but kind of like an inverse duplicate.

I need the SQL syntax to display color1and color2 which in this case would return only 2 records - red/orange and blue/green. And it doesn't matter if it returns red/orange or orange/red, as long as the pair is returned only once. So I'd run the query and get:

color1...........color2-----------------------
red..............orange
blue.............green

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL "inverse duplicates" query?

by avhohlov In reply to SQL "inverse duplicates" ...

See next union query:

SELECT DISTINCT IIf(Colors1.Color1<Colors1.Color2,Colors1.Color1,Colors1.Color2) AS Color1, IIf(Colors1.Color1<Colors1.Color2,Colors1.Color2,Colors1.Color1) AS Color2
FROM Colors AS Colors1 LEFT JOIN Colors AS Colors2 ON (Colors1.Color1 = Colors2.Color2) AND (Colors1.Color2 = Colors2.Color1)
WHERE Colors2.Color1 Is Not Null
UNION SELECT DISTINCT Colors1.Color1, Colors1.Color2
FROM Colors AS Colors1 LEFT JOIN Colors AS Colors2 ON (Colors1.Color1 = Colors2.Color2) AND(Colors1.Color2 = Colors2.Color1)
WHERE Colors2.Color1 Is Null;

First query selects orange&red, second - blue&green.

Collapse -

SQL "inverse duplicates" query?

by Mr. Origami In reply to SQL "inverse duplicates" ...

I don't understand. There is only 1 table - tblColors. It has only two fields - Color1 and Color2.

So - why did you code "Colors1.Color1" etc...?

Collapse -

SQL "inverse duplicates" query?

by avhohlov In reply to SQL "inverse duplicates" ...

Correction to previous answer.
Simply replace Colors with tblColors.
Colors1&Colors2 are aliases (temporary names) of Colors (tblColors).

Collapse -

SQL "inverse duplicates" query?

by Mr. Origami In reply to SQL "inverse duplicates" ...

Still extremely confusing with the word Colors every other word! Any chance of giving this to me again with the following: table = T1, fields = C1 and C2, aliases = myC1 and myC2?

Collapse -

SQL "inverse duplicates" query?

by avhohlov In reply to SQL "inverse duplicates" ...

--table = T1, fields = C1 and C2,
table aliases myT1_1 and myT1_2 (both T1 aliases)
field aliases myC1 and myC2

SELECT DISTINCT IIf(myT1_1.C1<myT1_1.C2,myT1_1.C1,myT1_1.C2) As myC1, IIf(myT1_1.C1<myT1_1.C2,myT1_1.C2,myT1_1.C1) As myC2
FROM T1As myT1_1 LEFT JOIN T1 As myT1_2 ON (myT1_1.C1 = myT1_2.C2) AND (myT1_1.C2 = myT1_2.C1)
WHERE myT1_2.C1 Is Not Null
UNION SELECT DISTINCT myT1_1.C1 As myC1, myT1_1.C2 As myC2
FROM T1 AS myT1_1 LEFT JOIN T1 AS myT1_2 ON (myT1_1.C1 = myT1_2.C2) AND(myT1_1.C2 = myT1_2.C1)
WHERE myT1_2.C1 Is Null;

Collapse -

SQL "inverse duplicates" query?

by Mr. Origami In reply to SQL "inverse duplicates" ...

That did it! Thank you so much!!!

Collapse -

SQL "inverse duplicates" query?

by Mr. Origami In reply to SQL "inverse duplicates" ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums