SQL joining a table with itself

Question

I have a table looking like this:

+-----------+--------+------------+
| member_id |  name  | partner_id |
+-----------+--------+------------+
|         1 | John   | 2          |
|         2 | Eva    | 1          |
|         3 | Peter  | 4          |
|         4 | Mia    | 3          |
|         5 | Rachel | null       |
+-----------+--------+------------+

I want to display member name and name of its partner. I also need to display members without one. However, this is what I get when I use RIGTH JOIN:

SELECT m1.name, m2.name
FROM members m1
RIGHT JOIN members m2 ON m1.member_id=m2.partner_id

+--------+--------------+
|  name  | partner_name |
+--------+--------------+
| John   | Eva          |
| Eva    | John         |
| Peter  | Mia          |
| Mia    | Peter        |
| Rachel | null         |
+--------+--------------+

I want them to be displayed only once as a couple. So when I get John - Eva, I don't want Eva - John to be displayed. Can this be done?

Which DBMS? You should always tag SQL questions with the DBMS you are using. — Thorsten Kettner, Sep 20 '20 at 18:19
Don't ever use right outer joins. They can be very hard to read. Always use left outer joins instead. — Thorsten Kettner, Sep 20 '20 at 18:23

Gordon Linoff · Answer 1 · 2020-09-20T18:09:15.513

5

Use left join:

select m.*, mp.name as partner_name
from members m left join
     members mp
     on m.partner_id = mp.member_id;

You have duplicates in your data, but they seem to be symmetric. A simple way around that is:

select m.*, mp.name as partner_name
from (select m.*
      from members m
      where m.member_id < m.partner_id or
            m.partner_id is null
     ) m left join
     members mp
     on m.partner_id = mp.member_id;

Here is a db<>fiddle.

edited Sep 20 '20 at 18:09

answered Sep 20 '20 at 17:48

Gordon Linoff

1,242,037
58
646
786

Does not retrieve Rachel name. – June7 Sep 20 '20 at 18:04
@June7 . . . The subquery just didn't return the name. I added a db<>fiddle to illustrate that it does work. – Gordon Linoff Sep 20 '20 at 18:09

score -1 · Answer 2 · edited Sep 20 '20 at 19:05

-1

@Mureinik's answer is absolutely correct. In case, if you want to hide null records in name1.

select m3.name1,m3.name2 from (
SELECT     m1.name as name1, m2.name as name2
FROM       members m1 
RIGHT JOIN members m2 ON m1.member_id = m2.partner_id AND m1.name > m2.name) as m3 where length(m3.name1)>1

edited Sep 20 '20 at 19:05

answered Sep 20 '20 at 18:21

awan

27
7

No it is not "absolutely correct", because it returns some partners as singles. And your query doesn't show singles. Use dbfiddle to check this. Using Gordon's fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=00f97201b4fb8db7209440bb39563d89 – Thorsten Kettner Sep 20 '20 at 18:32
1

The `code snippet` button is for JavaScript and HTML by the way, not for SQL. Use the `code sample` button (`{}`) instead. – Thorsten Kettner Sep 20 '20 at 18:35

SQL joining a table with itself

2 Answers2