CODEWITHSUNDEEP
mysqlno-duplicates
user3649739
user3649739

Reputation: 1869

Find records without reciprocal record in a mysql table

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?

Upvotes: 0

Views: 73

Answers (1)

Carsten Massmann
Carsten Massmann

Reputation: 28196

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.

Upvotes: 1

Related Questions