CODEWITHSUNDEEP
mysqlselect
Anton Sementsov
Anton Sementsov

Reputation: 1246

Mysql Select reciprocal pairs of records, without duplicates

I have a table with two columns:

  1. person_id
  2. person_id with whom 1st field id is in cooperation

I need to select all cooperation pairs, it is easy but in what is the problem: table have data like: 987 - 102, 103 - 104, 104 - 103, 21 - 102. As the result with such data i should have 3 cooperation pairs 987 - 102, 103-104, 21-102, as you see 103 - 104 and 104 - 103 records have the same logic, how can I avoid duplicating of them. Any idea?

Thanks, and best regards. Anton.

Upvotes: 7

Views: 1996

Answers (2)

spencer7593
spencer7593

Reputation: 108420

If preserving the order of the elements in each "pair" is not important, see the answer from eggyal. That query returns a result set that is slightly different than the one you specified, it returns pair 102-987 instead of 987-102. It also eliminates any "duplicate" pairs that appear in the table.

If preserving the order of the elements in each pair is important, and you want to return the "smaller - larger" rather than "larger - smaller" when both of those "matching" pairs are present, the you can use something like this:

SELECT c.col1, c.col2
  FROM mytable c
  LEFT
  JOIN mytable d ON d.col1 = c.col2 AND d.col2 = c.col1 AND d.col1 <> d.col2
 WHERE (d.col1 IS NULL OR d.col1 > c.col1)

To eliminate all duplicate pairs AND "matching" pairs, add a GROUP BY clause or the DISTINCT keyword, e.g.

SELECT c.col1, c.col2
  FROM mytable c
  LEFT
  JOIN mytable d ON d.col1 = c.col2 AND d.col2 = c.col1 AND d.col1 <> d.col2
 WHERE (d.col1 IS NULL OR d.col1 > c.col1)
 GROUP BY c.col1, c.col2

NOTES:

SQL Fiddle here: http://sqlfiddle.com/#!2/1d9e7/1 and here: http://sqlfiddle.com/#!2/1d9e7/2

The comparison operators are not null-safe, they may not return the resultset you want when either col1 or col2 contains a NULL value. (The query could be modified to handle NULL values for col1 and/or col2.) As written, both queries would return, for example, both (1,NULL) and (NULL,1) if those "matching" "pairs" are in the table. (It boils down to the question of whether you want to consider NULL values to match or not.)

Also note, both queries will return rows where col1=col2.

Note the first query does NOT eliminate "duplicate" rows that exist in the table. That is, if a duplicate "pair" e.g (202,101) appears in two different rows, then both will be returned (unless the query returns at least one row with a "matching" pair: (101,202).)

It wasn't clear what result set you wanted returned in those cases, so the first query shows the pattern for eliminating ONLY rows (larger,smaller) when a matching (smaller,larger) pair is in the resultset.

The second query eliminates ALL duplicates and "matching" pairs.

Upvotes: 3

eggyal
eggyal

Reputation: 125865

You could use MySQL's LEAST() and GREATEST() functions, together with DISTINCT:

SELECT DISTINCT LEAST(a, b), GREATEST(a, b) FROM mytable

Upvotes: 10

Related Questions