Filter Data

• George Damien Varkey

  SSC-Addicted

  Points: 440

  More actions

  August 25, 2006 at 12:50 am

  #70931

  EmpID    ID      NO    Name

    1        123    111    George

    1        NULL   111   George

    2        456     222   George

    2        475     222   Raj

    2        NULL   222    Raj

    3        NULL   333   Thomas

  I want only those records where the Name repeats more than once and EmpID is NULL.

  Desired Output

  EmpID    ID      NO    Name

    1        NULL   111   George

    2        NULL   222    Raj

  Thanks

   

   

   

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  August 25, 2006 at 1:43 am

  #656777

  declare @test-2 table (empid int, id int, no int, name varchar(100))

  insert @test

  select 1, 123, 111, 'George' union all

  select 1, NULL, 111, 'George' union all

  select 2, 456, 222, 'George' union all

  select 2, 475, 222, 'Raj' union all

  select 2, NULL, 222, 'Raj' union all

  select 3, NULL, 333, 'Thomas'

  SELECT DISTINCT t.*

  FROM  @Test t

  INNER JOIN (

     SELECT  EmpID

     FROM  @Test

     GROUP BY EmpID

     HAVING  COUNT(*) > 1

   &nbsp z ON z.EmpID = t.EmpID

  WHERE  t.ID IS NULL

  ---

  N 56°04'39.16"
  E 12°55'05.25"