Query Question - Need to list accounts with only 1 contact

Question

Query Question - Need to list accounts with only 1 contact

BarbD

Mr or Mrs. 500

Points: 507
More actions
February 8, 2008 at 2:25 pm

#69563

I have an account and contact table. An account may have 1 or more contacts. How do I identify accounts that have ONLY 1 contact? I need to show both the account and the contact.
Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

This approach should work. Keep in mind that from your description, I have no idea what your table DDL looks like. This is a wild stab in the dark. You'll have to take this approach and fit it to your tables but it should be close.

DECLARE @Contacts TABLE (ContactID int, Contact varchar(20))

INSERT INTO @Contacts

SELECT 1, 'Bob' UNION ALL

SELECT 2, 'Nick' UNION ALL

SELECT 3, 'SALLY' UNION ALL

SELECT 4, 'Fred'

DECLARE @Accounts TABLE (AccountID int,

ContactID int,

Value varchar(20))

INSERT INTO @Accounts

SELECT 1, 1, 'Act 1, 2 Contacts' UNION ALL

SELECT 1, 2, 'Act 1, 2 Contact' UNION ALL

SELECT 2, 4, 'Act 2, 1 Contact'

SELECT *

FROM @Accounts a1

INNER JOIN (

SELECT AccountID

FROM @Accounts

GROUP BY AccountID

HAVING COUNT(*) = 1

) a2

ON a1.AccountID = a2.AccountID

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

That should do it... nicely done, John.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BarbD Mr or Mrs. 500 Points: 507 More actions · Answer 3

Thank you very much - this was just what I was looking for!

Barbara

John Rowan SSC Guru Points: 56440 More actions · Answer 4

No problem. The key to this one is the derived table. If you're not real familiar with derived tables, I suggest searching SSC and reading up on them. There's also info in BOL. Understanding how and when to use derived tables can greatly simplify your code and they can be a great tool for query tuning for helping with replacing cursors or correlated subqueries.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden