find duplicates in opposing fields

• TH-354469

  Grasshopper

  Points: 13

  More actions

  August 1, 2006 at 11:33 am

  #68734

  Hi

   

  I'm looking for a query that will check for duplicates (or more exactly the lack of) in opposing fields.

  lets say that the table is called tableA, the fields are field1 and field2. I am looking for records where field1=field2 AND field2=field1

  any suggestions gratefully accepted.

   

   

   

   

  ---

  Kindest Regards,

  TH

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  August 2, 2006 at 12:43 am

  #652730

  Try:

  SELECT a.field1 AS a1, b.field2 AS b2, a.field2 AS a2, b.field1 AS b1

  FROM TableA AS a

     INNER JOIN TableA as b ON a.field1=b.field2 AND a.field2=b.field1

  ORDER BY a.field1, a.field2

  Andy