[SQL] Distinct multi-columns

Question

Hi,

For this table:

mysql> select * from work;
+------+---------+-------+
| code | surname | name  |
+------+---------+-------+
|    1 | John    | Smith |
|    2 | John    | Smith |
+------+---------+-------+

I'd like to get the pair of code where the names are equal, so I do this:

select distinct A.code, B.code from work A, work B where A.name = B.name group by A.code, B.code;

However, I get the follow result back:

+------+------+                                                                                         
| code | code |                                                                                         
+------+------+                                                                                         
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    2 |
+------+------+

As you can see, This result has 2 duplicates, obviously from a cartesian product. I'd like to find out how I can do this such that it outputs only:

+------+------+                                                                                         
| code | code |                                                                                         
+------+------+                                                                                         
|    1 |    2 |
+------+------+

Any clue? Thanks!

Answer 1

try this

  Select A.Code, B.Code
  From work a
     Join work b
         On A.surname = b.surname
            And A.Name = B.Name
            And A.Code > B.Code

You need to use A.Code > B.Code rather than != to eliminate dupes of the type

{1, 2} and {2, 1}

(If you only care about when the name is the same and not the surname, eliminate that predicate from the join condition)

Answer 2

This should work (assuming code is the primary key):

SELECT A.code, B.code
FROM work A, work B
WHERE A.name = B.name AND A.code < B.code