CODEWITHSUNDEEP
sqlsql-server
Bauerhof
Bauerhof

Reputation: 165

Selecting unique values from self-referencing table

Suppose we have the following data in the table named My_Tabel:

╔═══════════╦═════════════╦════════════╗
║  ID       ║ Person_Name ║ Partner_ID ║ 
╠═══════════╬═════════════╬════════════╬
║  101      ║  John       ║ 3          ║ 
║  100      ║  Miller     ║ 0          ║ 
║  3        ║  Ruby       ║ 101        ║ 
║  180      ║  Jack       ║ 0          ║ 
║  199      ║  George     ║ 65         ║
║  23       ║  Joseph     ║ 0          ║
║  34       ║  Fredrick   ║ 117        ║
║  117      ║  Jinan      ║ 34         ║
║  122      ║  Verena     ║ 0          ║
║  65       ║  Mary       ║ 199        ║
╚═══════════╩═════════════╩════════════╝

Where 0 values in Partner_ID Column indicates that he/she is single.

We need to display partnered persons without repeating or duplication, the desired result should look like:

╔═════════════╦══════════════╗
║ Person_Name ║ Partner_Name ║ 
╠═════════════╬══════════════╬
║  John       ║  Ruby        ║ 
║  George     ║  Mary        ║
║  Fredrick   ║ Jinan        ║
╚═════════════╩══════════════╝

what is the best SQL query that returns the above results?

I'm using this code:

SELECT    
  t1.Name, t2.Name          
FROM  My_Tabel t1
INNER JOIN  My_Tabel t2 ON (t2.ID = t1.Partner_ID)

but it the returned result is:

╔═════════════╦══════════════╗
║ Person_Name ║ Partner_Name ║ 
╠═════════════╬══════════════╬
║  John       ║  Ruby        ║ 
║  Ruby       ║  John        ║
║  George     ║  Mary        ║
║  Mary       ║  George      ║
║  Fredrick   ║  Jinan       ║
║  Jinan      ║  Fredrick    ║   
╚═════════════╩══════════════╝

how the SQL statement should be updated (or replaced with another) to get the desired results?

Upvotes: 1

Views: 226

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Just add a condition to get one side of each pair:

SELECT t1.Name, t2.Name          
FROM My_Table t1 INNER JOIN
     My_Table t2
     ON (t2.ID = t1.Partner_ID)
WHERE t1.ID < t2.ID;

Upvotes: 1

Related Questions