Ezzedeen Saghier Ezzedeen Saghier - 4 months ago 10
SQL Question

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?

Answer

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;
Comments