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))

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

Name fields should be split, ideally.

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