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.
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;
Shows what is selected and what I need to filter away
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?
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
.
cId | cPlayerA | cPlayerB --: | -------: | -------: 1 | 2001 | 1001 3 | 4001 | 3001
Brilliant. Thought this was impossible. A big thanks!
Welcome @PeterEricsson! If my answer properly responded to your question, then accept it by clicking the check sign... Thanks.
Done. Was a bit difficult to find.. It was my first question...