MYSQL: How to select all attributes of all duplicate entries?
There are a few way of doing this and depends on the size of your initial table. 

Option 1: 

/** This gives you all the surnames that are duplicated **/
Create Temporary Table tmpData
(surname CHAR(100) NOT NULL,
counter INT NOT NULL DEFAULT 1,
Primary Key (surname));
Insert Into tmpData
Select surname, count(1) as recs
From (jasenet)
Group BY 1
Having recs &gt; 1;

/** This gives you all the SINS that are duplicated **/
Create Temporary Table tmpData2
(sin CHAR(100) NOT NULL,
counter INT NOT NULL DEFAULT 1,
Primary Key (surname));
Insert Into tmpData
Select sin, count(1) as recs
From (jasenet)
Group BY 1
Having recs &gt; 1;

Select DISTINCT A.* 
From jasenet A, tmpData B, tmpData2 C
Where A.surname = B.surname
Or A.sin = C.sin;


Option 2 (Horribly slow -- never do)

Select A.userid, B.userid, 
    IF(A.surname = B.surname, 1, 0) as sameSurname,
    IF(A.sin = B.sin, 1, 0) as sameSIN
From (jasenet A, jasenet B)
Where A.userid != B.userid;


Option 3 -- Probably what you're looking for

Select A.surname, group_concat(A.userid) as users
From (jasenet A)
Group By 1
Having COUNT(A.surname) &gt; 1;

Select A.sin, group_concat(A.userid) as users
From (jasenet A)
Group By 1
Having COUNT(A.sin) &gt; 1;


So I gave a few differences but there are a few things I should mention. You are using GROUP BY without an aggregate function which in general means you can have non-deterministic results. Also it's just not how you use group by. What I tell my developers is when using group by it's as if you're building a new table with X fields as the PRIMARY KEY of that table. 

Do not use #2 as it has to basically do 1 full scan for every userid, I just wanted to give it to you so you can see that you can double link a table (as long as it's not a temporary table).

Option 1 Is similar to #3 but uses temp tables which are extremely fast and I figured you can then use the temp tables to do your linking on another table. Just remember, temporary tables are gone once you CLOSE the session.

Let me know if this is not what you were looking for