Unanswered: how to select a unique couple from data???
i have a table member with emails
i want to insert the emails from member to table connects where connects has two collumns
the one is email and the other connectedWith_email
the problem is that i can't find a way to select all the emails that are not yet connected so that a connection is unique
for example i don't want to have a connection for example:
email1, email2 and then have the same connection email2, email1
the query that does that is:
select distinct m1.email, m2.email from member m1, member m2
where m1.email!=m2.email and not exists
(select c.email, c.connectedWith_email from connects c where c.email=m1.email and c.connectedWith_email=m2.email);
i don't want to re_connect for example
mail2 | mail1
because its the same connection as the first.....
this the query i use...
select distinct m1.email, m2.email from member m1, member m2 group by m1.email, m2.email having m1.email!=m2.email and NOT EXISTS (select c.email, c.connectedWith_email from connects c where (c.email=m1.email and c.connectedWith_email=m2.email) or(c.email=m2.email and c.connectedWith_email=m1.email))