Results 1 to 3 of 3

Thread: Self Join

  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Red face Unanswered: Self Join

    Hi,

    I have a table like this

    Field 1 Field 2 Field 3
    ------- -------- --------
    1 dsfsdf 1234
    2 tgweg 8778
    3 dfvsdf 8778
    4 wefwe 9938
    5 sjdsjn 2398

    etc.

    I want to select the rows (like 2nd & 3rd ), as it has equal value in the field 3.

    please help

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Self Join

    Your title "self join" suggests that you want to do this:

    select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
    from table t1
    join table t2 on t1.field2 = t2.field3 and t1.field1 != t2.field1;

    However, what if there were 3 or more records with same field3 value?

    A more general solution is:

    select *
    from table
    where field3 in
    ( select field3
    from table
    group by field3
    having count(*) > 1
    );

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Many thanks, it is working

Posting Permissions

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