Query for Parent containing only ONE type of child

[Warren]

I am trying to find all offices that contain just ONE type of member. Each
office can contain many types of members but I want to find those that have
just one type. I need to be able to assign (within the code) the type I'm
looking for too.
Thx, Warren

ORGANIZATION
OrgID (pk)
OrgName

MEMBERS
MemID (pk)
MemberName
OrgID (fk)

 

[Douglas J. Steele]

How do you identify the type of member?

 

[Warren]

Sorry 'bout that. There is a MemberTitle field in the Members table and it
is a text field.

 

[Klatuu]

This would be pretty much like the query I help you with earlier using the
Group By.
But in this case, you would add the member title field and group on it as
well.
Then add the member title field a second time, but instead of Group By,
select Count. Then, in the Criteria row for the second instance of member
title type in 1
It will then return only those orgs that have 1 member.

 

[Warren]

Sorry, but doesn't work properly. The code I come up with following your
directions...
SELECT Company.CompanyName, Count(Contact.Title) AS CountOfTitle
FROM Company LEFT JOIN Contact ON Company.CompanyID = Contact.CompanyID
GROUP BY Company.CompanyName, Contact.Title
HAVING (((Contact.Title)="Broker Associate") AND ((Count(Contact.Title))=1));

It will return a company that has two members, one with a title of "Managing
Broker" and the other with "Broker Associate".
I want only those offices that contain only "Broker Associates" - one or many.
Let me know what I forgot. -Warren

 

[Douglas J. Steele]

Try:

SELECT OrgID, OrgName
FROM ORGANIZATION
WHERE OrdID IN
(SELECT OrgID
FROM MEMBERS
GROUP BY OrgID
HAVING COUNT(MemberType) = 1)

 

[John Spencer]

If all you want is a list of Organzations that have members of one specific
type, the following should work. But it will probably be slov if you have a
large set of records.

SELECT Distinct OrgId, OrgName
FROM Organization INNER JOIN Members
On Organization.OrgID = Members.OrgID
WHERE NOT Exists
(SELECT *
FROM Members as Tmp
WHERE Tmp.OrgID = Organization.OrgID
AND Tmp.MemberTitle <> "Some Value")
AND Members.MemberTitle = "Some Value"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

SELECT Company.CompanyName, Count(Contact.Title) AS CountOfTitle
FROM Company LEFT JOIN Contact ON Company.CompanyID = Contact.CompanyID
GROUP BY Company.CompanyName, Contact.Title
HAVING (((Contact.Title)="Broker Associate") AND ((Count(Contact.Title))=1));

It will return a company that has two members, one with a title of "Managing
Broker" and the other with "Broker Associate".
I want only those offices that contain only "Broker Associates" - one or many.

SELECT C1.CompanyName
FROM Company AS C1
WHERE EXISTS (
SELECT *
FROM Contact AS T1
WHERE C1.CompanyID = T1.CompanyID
AND T1.Title = 'Broker Associate');

Perhaps better performance for Jet:

SELECT DISTINCT C1.CompanyName
FROM Company AS C1
INNER JOIN Contact AS T1
ON C1.CompanyID = T1.CompanyID
WHERE T1.Title = 'Broker Associate';

Jamie.

--

 

[Douglas J. Steele]

SELECT C1.CompanyName
FROM Company AS C1
WHERE EXISTS (
SELECT *
FROM Contact AS T1
WHERE C1.CompanyID = T1.CompanyID
AND T1.Title = 'Broker Associate');

Perhaps better performance for Jet:

SELECT DISTINCT C1.CompanyName
FROM Company AS C1
INNER JOIN Contact AS T1
ON C1.CompanyID = T1.CompanyID
WHERE T1.Title = 'Broker Associate';

Those queries suffer from the same problem previously mentioned: they will
return companies that have two (or more) member types, one of which is
Broker Associates. I believe he only wanted those companies that just have
Broker Associates and no other titles.

 

[Jamie Collins]

Those queries suffer from the same problem previously mentioned: they will
return companies that have two (or more) member types, one of which is
Broker Associates. I believe he only wanted those companies that just have
Broker Associates and no other titles.

Fair enough Add another test:

SELECT C1.CompanyName
FROM Company AS C1
WHERE EXISTS (
SELECT *
FROM Contact AS T1
WHERE C1.CompanyID = T1.CompanyID
AND T1.Title = 'Broker Associate')
AND 1 = (
SELECT COUNT(*)
FROM Contact AS T1
WHERE C1.CompanyID = T1.CompanyID);

Jamie.

--

 

[Jamie Collins]

Fair enough Add another test:

Oops! Posted to quick:

SELECT C1.CompanyName
FROM Company AS C1
WHERE EXISTS (
SELECT *
FROM Contact AS T1
WHERE C1.CompanyID = T1.CompanyID
AND T1.Title = 'Broker Associate')
AND 1 = (
SELECT COUNT(*)
FROM (
SELECT T1.Title, T1.CompanyID
FROM Contact AS T1
GROUP BY T1.Title, T1.CompanyID
) AS DT1
WHERE C1.CompanyID = DT1.CompanyID
);

Jamie.

--