asked on

SQL Query - If two records contain duplicate field....

Hi,
Using a given ClientID I can check what accounts exist. Client 10 returns these results..

SELECT Person_ID, Client_ID, Account_ID
FROM Table1
WHERE Client_ID = 10

Open in new window

----CURRENT RESULTS-----
ClientID   |    PersonID   |   AccountID
10          |       1      |       999
10          |       2      |       999
10          |       1      |       555

Open in new window

This tells me that there is one account (999) that is owned by two Persons (1 and 2) and one account (555) singulary owned by Person 1.

How do I extend this query to only return accounts that are not jointly owned. That is - only owned by one PersonID

----DESIRED RESULTS-----
ClientID   |    PersonID   |   AccountID
10          |       1       |        555

Open in new window

Thanks for any input.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

answer_dude

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

That should have been

SELECT Person_ID, Client_ID, Account_ID
FROM
(
SELECT Person_ID, Client_ID, Account_ID, TestCount = Count(*) over (partition by Account_ID)
FROM Table1
WHERE Client_ID = 10
) X
WHERE TestCount = 1

cyberkiwi

Or this (either will work)

SELECT * FROM Table1
WHERE Client_ID = 10
AND Account_ID IN (
	SELECT Account_ID
	FROM Table1
	WHERE Client_ID = 10
	group by Account_ID
	having COUNT(*) = 1
	)

Open in new window

SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial