Returning Duplicate Records

I have a Transactions table w/the following columns (all VarChar):
CustomerID,
Customer_Name,
User_Name
And and Admin table w/the following columns (VarChar):
User_Name,
Company_ID
Now I want to return any records where the CustomerID is duplicated for
different Customer_Names when Grouped by Company_ID. Here's a sample of wha
t
the returned data will look like:
Company_ID Customer_ID Customer_Name
R9 321 Ted Smith
R9 321 Rob Wand
Here's my query:
SELECT COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME,
COUNT (CUSTOMER_ID) AS NUM_OCCUR
FROM TRANSACTIONS
INNER JOIN ADMIN ON
ADMIN.USER_NAME = TRANSACTIONS.USER_NAME
GROUP BY COMPANY_ID, CUSTOMER_NAME, CUSTOMER_ID
HAVING (COUNT (CUSTOMER_ID) > 1)
ORDER BY COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME
This query only gets me half way there, as I can only visually inspect
what's returned. Here's a sample of the returned data:
Company_ID Customer_ID Customer_Name Num_Occur
R12 1 Jim Jones 2
R9 1000 Chris B 3
R9 1000 Brian P 5
R9 1001 Dave B 8
In this example, I ONLY want to return records 2 & 3 where the Company_ID,
and Customer_ID are the same, but the Customer_Names differ.On Thu, 19 Jan 2006 08:07:03 -0800, Eric wrote:

>I have a Transactions table w/the following columns (all VarChar):
>CustomerID,
>Customer_Name,
>User_Name
>And and Admin table w/the following columns (VarChar):
>User_Name,
>Company_ID
>Now I want to return any records where the CustomerID is duplicated for
>different Customer_Names when Grouped by Company_ID. Here's a sample of wh
at
>the returned data will look like:
>Company_ID Customer_ID Customer_Name
>R9 321 Ted Smith
>R9 321 Rob Wand
>Here's my query:
>SELECT COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME,
> COUNT (CUSTOMER_ID) AS NUM_OCCUR
>FROM TRANSACTIONS
>INNER JOIN ADMIN ON
> ADMIN.USER_NAME = TRANSACTIONS.USER_NAME
>GROUP BY COMPANY_ID, CUSTOMER_NAME, CUSTOMER_ID
>HAVING (COUNT (CUSTOMER_ID) > 1)
>ORDER BY COMPANY_ID, CUSTOMER_ID, CUSTOMER_NAME
>This query only gets me half way there, as I can only visually inspect
>what's returned. Here's a sample of the returned data:
>Company_ID Customer_ID Customer_Name Num_Occur
>R12 1 Jim Jones 2
>R9 1000 Chris B 3
>R9 1000 Brian P 5
>R9 1001 Dave B 8
>In this example, I ONLY want to return records 2 & 3 where the Company_ID,
>and Customer_ID are the same, but the Customer_Names differ.
Hi Eric,
Try if this works for you:
SELECT a.Company_ID, t.CustomerID, t.Customer_Name
FROM Admin AS a
INNER JOIN Transactions AS t
ON t.User_Name = a.User_Name
WHERE EXISTS
(SELECT *
FROM Transactions AS t2
WHERE t2.CustomerID = t.CustomerID
AND t2.User_Name <> t.User_Name)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP