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.
LVL 2
SM17CHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
answer_dudeCommented:
Something like this should work.
SELECT Table1.ClientID, Table1.PersonID, Table1.AccountID
FROM Table1
WHERE (((Table1.AccountID) In (SELECT T1.AccountID
FROM Table1 as T1
GROUP BY T1.ClientID, T1.AccountID
HAVING (((Count(T1.PersonID))=1))))) and Table1.ClientID=10;

Open in new window

0
cyberkiwiCommented:
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
0
cyberkiwiCommented:
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

0
Alpesh PatelAssistant ConsultantCommented:
SELECT * FROM Table
WHERE Account_ID IN (
        SELECT Account_ID
        FROM Table1
        group by Account_ID
        having COUNT(*) = 1
        )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.