For this table each column has distinct rows i.e count(Name) = count(Spouse_Name) = count(*) Want to write a SQL query where pairs (Name and Spouse_Name) are distinct i.e Alex Sandra and Sandra Alex are same.
The output should be following:
Note: Self Inner Join if possible.
Assuming that name and spouse_name will never be equal in the same row you can use NOT EXISTS like this:
select t.*
from tablename t
where t.name < t.spouse_name
or not exists (
select 1 from tablename
where name = t.spouse_name and spouse_name = t.name
)
This code will work in any database since it uses standard SQL.
For MySql and this sample data you can do this self join:
select distinct
least(t1.name, t1.spouse_name) name,
greatest(t1.name, t1.spouse_name) spouse_name
from tablename t1 inner join tablename t2
on t1.name = t2.spouse_name and t2.name = t1.spouse_name
Although the same results can be obtained without a join:
select distinct
least(t1.name, t1.spouse_name) name,
greatest(t1.name, t1.spouse_name) spouse_name
from tablename t1
And another query with a self join:
select t1.name, t1.spouse_name
from tablename t1 inner join tablename t2
on t2.spouse_name = t1.name and t2.name = t1.spouse_name
where t1.name < t1.spouse_name
Untested:
select distinct t1.name, t1.spouse_name
from table t1
inner join table t2
on concat(t1.name, t1.spouse_name) = concat(t2.spouse_name, t2.name)
I couldn't find the table name in your post so I used the name table.
