Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: help filtering rows which fields have the same value in another row

    I am trying to figure out how to make my select statment

    here is the table
    Table
    --------
    col1 col2

    1 2
    2 1
    3 4
    4 3
    1 4
    4 1



    What I would like to do is creat a select statment which will return only 1 row
    if any other row if there is another row which col1 matches current rows col2
    example the results would be returned like this

    col1 col2
    1 2
    3 4
    1 4

    so the select statment filters out any of the other rows where the
    col 1 was equal to the other rows col2 and the col2 was equal to that same other rows col 1.

    I am sure the answer is simple I cant wrap my head around it.

    Adam

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select this.col1 
         , this.col2
      from daTable as this
    inner
      join daTable as that
        on that.col2 = this.col1
       and that.col1 = this.col2
     where this.col1 < this.col2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    2

    Thumbs up thanx

    Your brilliant!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you, thank you, i'll be here all week

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

Posting Permissions

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