Select query w/ group by

[jsdba]

Hi Experts,

I'm trying to write a query that will return only the record that only have RoleID = 1. Group BY ProjectNum. E.g. the record that should be returned is 18003-101-1 because all other project groupings include records with RoleIDs different from 1.

Last bit of info to consider, all groups will have at least one record, that record will have RoleID = 1

See attachment

 

[theDBguy]

Hi. You could try something like this:

SELECT ProjectNum, EmployeeID
FROM tblProjectTeam
WHERE RoleID=1
GROUP BY ProjectNum, EmployeeID
HAVING Count(*)=1

 

[plog]

Can RoleID be null or less than 1?

 

[jsdba]

Can RoleID be null or less than 1?

I'll say this it shouldn't be null. that record would be an anomaly. Also cannot be less than 1.

 

[plog]

You are going to need a subquery to do this:

SELECT ProjectNum
FROM tblProjectTeam
GROUP BY ProjectNum
HAVING Max(RoleID)=1;

That will return all the ProjectNum values that meet your criteria. You can then get all the records by taking that query and making a new one with it and tblProjectTeam and joining them via ProjectNum

 

[jsdba]

You are going to need a subquery to do this:

SELECT ProjectNum
FROM tblProjectTeam
GROUP BY ProjectNum
HAVING Max(RoleID)=1;

That will return all the ProjectNum values that meet your criteria. You can then get all the records by taking that query and making a new one with it and tblProjectTeam and joining them via ProjectNum

Thank you plog. I'm working on it and so far it works with the testing table. I'll update final results later.