Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Unanswered: Removing duplicate results with one column different

    Hi

    I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.

    Values in the table
    Code:
    name          surname   status        Closed          Number                                
    ----------- --------- ----------- ------------- ----------------------------
    Jeff            Burns    closed        2012/01/01     142
    Tina            Drewmor  closed        2008/05/20     546
    Jeff            Burns    active        1900/01/01     354
    Kyle            Higgin   active        1900/01/01     851
    Tina            Drewmor  closed        2009/04/14     154
    The query I am using so far is:
    Code:
    select
      d.name,
      d.surname,
      s.status,
      s.closed,
      s.number
    from 
    d d inner join s s on d.number = s.number
    where
     s.status = 'closed'
    What I need to see in the results
    Code:
    name          surname   status        Closed          Number                               
    ----------- --------- ----------- ------------ -----------------------------
    Tina            Drewmor  closed        2008/05/20    546
    Tina            Drewmor  closed        2009/04/14    154
    Any help would be much appreciated

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    you will probably end up with a NOT EXISTS statement in the WHERE clause that looks for people with at least one open account.

  3. #3
    Join Date
    Aug 2012
    Posts
    3
    Quote Originally Posted by MCrowley View Post
    you will probably end up with a NOT EXISTS statement in the WHERE clause that looks for people with at least one open account.
    How would that be done? Use a sub query to count the active accounts?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #foo (
       FirstName  VARCHAR(9) NOT NULL
    ,  LastName   VARCHAR(9) NOT NULL
    ,  Active     BIT        NOT NULL
    ,  DateClosed DATETIME2  NULL
    ,  Number     INT        NOT NULL
    )
    
    INSERT INTO #foo
       VALUES ('Jeff', 'Burns',   0, '2012-01-01', 142)
    ,         ('Tina', 'Drewmor', 0, '2008/05/20', 546)
    ,         ('Jeff', 'Burns',   1, NULL,         354)
    ,         ('Kyle', 'Higgin',  1, NULL,         851)
    ,         ('Tina', 'Drewmor', 0, '2009/04/14', 154)
    
    SELECT *
       FROM #foo AS a
       WHERE NOT EXISTS (SELECT *
          FROM #foo AS b
    	  WHERE  1 = b.active
    	     AND b.FirstName = a.FirstName
    		 AND b.LastName  = a.LastName)
    
    DROP TABLE #foo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2012
    Posts
    3
    Quote Originally Posted by Pat Phelan View Post
    Code:
    CREATE TABLE #foo (
       FirstName  VARCHAR(9) NOT NULL
    ,  LastName   VARCHAR(9) NOT NULL
    ,  Active     BIT        NOT NULL
    ,  DateClosed DATETIME2  NULL
    ,  Number     INT        NOT NULL
    )
    
    INSERT INTO #foo
       VALUES ('Jeff', 'Burns',   0, '2012-01-01', 142)
    ,         ('Tina', 'Drewmor', 0, '2008/05/20', 546)
    ,         ('Jeff', 'Burns',   1, NULL,         354)
    ,         ('Kyle', 'Higgin',  1, NULL,         851)
    ,         ('Tina', 'Drewmor', 0, '2009/04/14', 154)
    
    SELECT *
       FROM #foo AS a
       WHERE NOT EXISTS (SELECT *
          FROM #foo AS b
    	  WHERE  1 = b.active
    	     AND b.FirstName = a.FirstName
    		 AND b.LastName  = a.LastName)
    
    DROP TABLE #foo
    -PatP
    This works. Thanks

Tags for this Thread

Posting Permissions

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