0

I have a table let's say it has Color in one field and Shape in the other but in no particular field order:

FIELD1   | FIELD2
---------|----------
Red      | Triangle
Red      | Square
Red      | Circle
Triangle | Red
Square   | Red

I am trying to find records that do NOT have a reciprocal record and create one. So in the above table there is no reciprocal for 

Red | Circle

while there are for

Red | Triangle
Red | Square

Is there a query that will a) Identify which records don't have reciprocals 2) Create those records?

Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43
user3649739
  • 1,829
  • 2
  • 18
  • 28

1 Answers1

1

You could do the following:

SELECT a.FIELD2, a.FIELD1 FROM tbl a 
LEFT JOIN tbl b ON b.FIELD2=a.FIELD1 AND b.FIELD1=a.FIELD2
WHERE b.FIELD1 is NULL

See here for a working example: SQLfiddle1

The SELECT joins the table onto itself by looking for the reciprocal record. If that record is not found then that record of the original table is listed with the order of columns reversed.

To create the missing records simply use the output in an INSERT statement like:

INSERT INTO tbl (FIELD1,FIELD2)
SELECT a.FIELD2, a.FIELD1 FROM tbl a 
LEFT JOIN tbl b ON b.FIELD2=a.FIELD1 AND b.FIELD1=a.FIELD2
WHERE b.FIELD1 is NULL

Demo of complete example SQLfiddle2.

Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43
  • Thank you I am testing that now (mine is a large table) but this seems workable into an update query for sure. FYI my other idea as since in fact the two fields are 5 digit integers I thought of creating a 'Product' field by multiplying each A and B field (e.g. 92451 | 90451 creates a Product field of 8362285401) and then I can just count(*) where that is 1 – user3649739 Sep 06 '15 at 14:35