Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    2

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nickm
    the query that does that is:
    that query works?

    so, um, what was your question again?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Quote Originally Posted by r937
    that query works?

    so, um, what was your question again?


    no it doesnt work.. my mistake

    i want to make connections between different emails...


    i have a table "members" filled with emails
    i have a table "connects" with fields email, connectedWith_email

    i want to fill "connects" with emails from "member" but i cant find a way to do that so that

    when two different emails connect for example

    ---email----|--connectedWith_email
    mail1 | mail2
    mail3 | mail4
    .... .....

    i don't want to re_connect for example
    ---email----|--connectedWith_email
    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))

    any suggestions?

    thanx anyway...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •