Help with building basic select statement

[Logitech]

Also posted in : microsoft.public.sqlserver.programming

Hi,

I'm not very experienced with creating queries and am having some difficulty
creating what I want. I was hoping someone could help me?

In layman's terms, here is what I am trying to create:

"Select all contacts where there is only one contact per company".

The code I tried was:

TABLES
account (i.e. company)
contact
-------------------------

SELECT *
FROM contact
WHERE (SELECT Count(*)
FROM contact JOIN account ON
(contact.accountid = account.accountid)
) = 1

However, this doesn't return anything. Help!

Thanks,

Darren

 

[Duane Hookom]

If your tables aren't overly large, this might meet your needs:

SELECT contact.*
FROM contact
WHERE contact.accountID Not IN (SELECT contact.accountid
FROM contact
GROUP BY contact.accountid
HAVING Count(*) >1)

 

[John Spencer]

Assuming you have a field in the Contact table that specifies the company.

SELECT *
FROM Contact
WHERE Account in
(SELECT Account
FROM Contact
GROUP BY Account
HAVING Count(Account) = 1)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Also posted in : microsoft.public.sqlserver.programming

Hi,

I'm not very experienced with creating queries and am having some difficulty
creating what I want. I was hoping someone could help me?

In layman's terms, here is what I am trying to create:

"Select all contacts where there is only one contact per company".

You don't need the company table at all, unless there is information in the
table that you want to display!

SELECT First(contactID) AS SoleContact
FROM contact
GROUP BY accountid
HAVING Count(*) = 1;