mysql join with the same table without duplicates

Question

I have a table instructors with the following strcture/data:

-----------------
|id  | name     |
-----------------
|1   | saad     |
|2   | imran    |
-----------------

I want to do a join with the same table without returning duplicate results.

If i do a simple join, the query returns 4 rows [saad, saad], [saad, imran], [imran, imran], [imran, saad].

And if I add a where clause it returns 2 rows [saad, imran], [imran, saad].

However, I only want to return 1 row, (i.e. if [saad, imran] was already returned, the query shouldn't return [imran, saad]).

Here's my query thus far:

SELECT DISTINCT
    i1.name AS name1,
    i2.name AS name2
FROM 
    instructors i1, instructors i2
WHERE
    i1.name != i2.name
ORDER BY
    name1, name2

score 8 · Accepted Answer · answered Mar 15 '13 at 17:47

8

Try this:

SELECT DISTINCT
   i1.name AS name1,
   i2.name AS name2
FROM 
   instructors i1, instructors i2
WHERE
   i1.id < i2.id
ORDER BY
   name1, name2

answered Mar 15 '13 at 17:47

niculare

3,629
1
25
39

Thank you, this works for my two rows but will this work regardless of the number of rows in the table? – Saad Imran. Mar 15 '13 at 17:52
Awesome, thanks for the help. I'll accept your answer as soon as it lets me! – Saad Imran. Mar 15 '13 at 17:54

score 4 · Answer 2 · answered Mar 15 '13 at 17:47

4

Try:

SELECT DISTINCT
    i1.name AS name1,
    i2.name AS name2
FROM 
    instructors i1, instructors i2
WHERE
    i1.name > i2.name
ORDER BY
    name1, name2

answered Mar 15 '13 at 17:47

mysql join with the same table without duplicates

2 Answers2