Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    15

    Unanswered: Getting id's that have more than one name

    I have a table of customers with Id and name. I want to extract all the customers with the same customer-id but

    have two or more different Customer-names. I am using SQL 2000 queries. How can I get the customer-id that has 2

    or more different Customer-names?

    For example;

    Input Table example:

    customer-id customer-name
    ----------- ------------
    200131 John Doe
    200145 Minnie Young
    300157 Dopie Jackson
    300157 Dopie Boowinkle
    300328 Mary Lou Redding

    Desired output:

    customer-id customer-name
    ----------- ------------
    300157 Dopie Jackson
    300157 Dopie Boowinkle



    Table DDL:
    CREATE TABLE customer-tbl(Col1 int, Col2 char(20))

  2. #2
    Join Date
    Feb 2004
    Posts
    193
    select [customer-id]
    , count(distinct [customer-name])
    from [customer-tbl]
    group by [customer-id]
    having count(distinct [customer-name]) > 1

    However, I would use the underscore("_") character and not a hyphen, I don't even know if hyphens are allowed, never seen one in a table or column name. Also name fields should be, FirstName, MiddleInit, LastName for example. Furthermore what are you trying to accomplish. Are you assuming that a person w/ a first name and a different last name has married and changed her name?

    ddave

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dolfandave View Post
    Also name fields should be, FirstName, MiddleInit, LastName for example.
    Name fields should be split, ideally.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2007
    Posts
    38
    Hi

    As the selected list by GROUP BY command does not carry coulmns which are not in the GROUP BY clause, probably more useful if you use the following get-around where condition. It works exactly like group by but it still carries other columns in a single query.

    if object_id('tempdb..#customer_tbl') is not null
    drop table #customer_tbl

    CREATE TABLE #customer_tbl(Col1 INT identity(1,1), Col2 int, Col3 varchar(40), Col4 DateTime)

    INSERT INTO #customer_tbl Values(200131, 'John Doe', '2010/01/01')
    INSERT INTO #customer_tbl Values(200145, 'Minnie Young', '2009/01/11')
    INSERT INTO #customer_tbl Values(300157, 'Dopie Jackson', '2008/01/01')
    INSERT INTO #customer_tbl Values(300157, 'Dopie Boowinkle', '2009/11/01')
    INSERT INTO #customer_tbl Values(300328, 'Mary Lou Redding1', '2007/01/06')
    INSERT INTO #customer_tbl Values(300328, 'Mary Lou Redding2', '2009/02/19')
    INSERT INTO #customer_tbl Values(300328, 'Mary Lou Redding3', '2010/04/01')
    INSERT INTO #customer_tbl Values(200146, 'Sophie Den', '2006/01/11')
    INSERT INTO #customer_tbl Values(200147, 'Simon Seng', '2006/01/11')
    INSERT INTO #customer_tbl Values(200148, 'Jim Nakamura', '2006/01/11')
    INSERT INTO #customer_tbl Values(200148, 'David Nakamura', '2006/01/11')
    INSERT INTO #customer_tbl Values(200149, 'William Park', '2006/01/11')

    SELECT * FROM #customer_tbl t
    WHERE
    (
    Select Count(*)
    FROM #customer_tbl
    WHERE t.Col2=Col2
    )>=2
    Order By Col2,Col3

Posting Permissions

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