Warm tip: This article is reproduced from stackoverflow.com, please click
mysql

Join two columns with IDs on different rows

发布于 2020-03-27 15:41:25

Problem description

I try to join or select unique rows from the same table from different columns and rows. The unique ID exists in both Column cPlayerA and cPlayerB. The table can grow dynamically and I cannot hard code the SQL.

Table Structure and data

CREATE TABLE tJoinTwoColumn(
cId int(11) NOT NULL AUTO_INCREMENT,
cPlayerA int(10) DEFAULT NULL,
cPlayerB int(10) DEFAULT NULL,
PRIMARY KEY (cId)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

#insert data
INSERT INTO tJoinTwoColumn (cId,cPlayerA,cPlayerB) VALUES (1,2001,1001);
INSERT INTO tJoinTwoColumn (cId,cPlayerA,cPlayerB) VALUES (2,1001,2001);
INSERT INTO tJoinTwoColumn (cId,cPlayerA,cPlayerB) VALUES (3,4001,3001);
INSERT INTO tJoinTwoColumn (cId,cPlayerA,cPlayerB) VALUES (4,3001,4001);

select * from tJoinTwoColumn;

Data returned

Shows what is selected and what I need to filter away

Shows what is selected and what I need to filter away

Code I tried

select distinct t1.cPlayerA , t1.cPlayerB  from tJoinTwoColumn t1
join  tJoinTwoColumn t2 on t1.cPlayerA = t2.cPlayerB
group by t1.cPlayerA , t1.cPlayerB;

But it returns 4 rows when I want to return only 2 rows in above example I would like row 2 and 4 to be filtered out in the select.

Is this possible?

Questioner
Peter Ericsson
Viewed
15
GMB 2020-01-31 16:28

I would use not exists with an inequality condition on id:

select t.*
from tJoinTwoColumn t
where not exists (
    select 1
    from tJoinTwoColumn t1
    where 
        least(t1.cPlayerA, t1.cPlayerB) = least(t.cPlayerA, t.cPlayerB)
        and greatest(t1.cPlayerA, t1.cPlayerB) = greatest(t.cPlayerA, t.cPlayerB)
        and t1.cId < t.cId
)

This will filter out true duplicates and "mirror" records, keeping the one that has the smallest id.

Demo on DB Fiddle:

cId | cPlayerA | cPlayerB
--: | -------: | -------:
  1 |     2001 |     1001
  3 |     4001 |     3001