Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: sql join question

    SELECT a.*
    FROM emp_table a INNER JOIN emp_table b
    ON a.emp_name=b.emp_name AND a.section_id=b.section_id AND a.dept_number=b.dept_number AND a.rowid>b.rowid
    order by 1 desc

    WHEN i run the above query i am getting 325 records.Actually i have only 25 duplicate records.WHEN i add distinct a.* i as shown below I am getting the correct result.I have a primary key on the column rowid.

    SELECT DISTINCT a.*
    FROM emp_table a INNER JOIN emp_table b
    ON a.emp_name=b.emp_name AND a.section_id=b.section_id AND a.dept_number=b.dept_number AND a.rowid>b.rowid
    order by 1 desc


    Whats the problem with my first query. all the columns in the query are of int data types.

    Thanks.

  2. #2
    Join Date
    Apr 2003
    Posts
    176
    I'm sorry emp_name in the above query is a varchar datatype.

    Thanks.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check this - may be you'll find your problem (name 'a' for 3 rows).

    drop table #test
    create table #test(id int identity,name varchar(10))

    insert #test(name) values('a')
    insert #test(name) values('b')
    insert #test(name) values('c')
    insert #test(name) values('a')
    insert #test(name) values('a')

    select a.*
    from #test a join #test b on a.name=b.name and a.id>b.id

    select distinct a.*
    from #test a join #test b on a.name=b.name and a.id>b.id

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by snail
    Check this - may be you'll find your problem (name 'a' for 3 rows).
    I started to write an explanation and realized that it's much easier to let him see the results on a small data set as you demonstrated. Good idea

Posting Permissions

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