sql with where clause, group by and (having?) (1 Viewer)

jsdba

Registered User.
Local time
Today, 08:25
Joined
Jun 25, 2014
Messages
164
Hi Experts,

I have need of a sql query to pull some data from the table below.

Premise:

Every project is assigned a project team. Every project have multiple phases. Each phase is assigned to a few employees. Each employee is assigned a role. I need a query that retrieves records for each project- phase that does not have an employee for the Back-up role. the query would produce the results highlighted below. Again the results should display records for project-phase where no back-up has been assigned. Thank you so much for your help.

Project 1001 Phase 3
Project 1002 Phase 2
Project 1005 Phase 2

1645816993971.png
 

plog

Banishment Pending
Local time
Today, 07:25
Joined
May 11, 2011
Messages
10,916
First ProjectTeam shouldn't be a table in your database---I am hoping its a query. Because by your definition, employees are not assigned directly to a Project, but to a Phase of a project. Also, because you have multiple employees and they can be assigned to multiple phases in multiple roles that means at least another table to sort all that out.

So, since I don't have your complete table structure I can only give you general advice. You need 2 datasets:

A. A table/query of all project/phases.

B. A query of all project/phases with someone who has a backup role.

Then you take A and B and make a new query out of them using this SQL:

Code:
SELECT A.Project, A.Phase FROM A LEFT JOIN B ON B.Phase=A.Phase AND B.Project=A.Project WHERE B.Phase IS NULL

That will give you all the phases without a backup.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 28, 2001
Messages
22,799
You don't seem to care about WHO is assigned, only how many are assigned.

Code:
SELECT ProjectNo, ProjectPhase FROM ProjectTeam GROUP BY ProjectNo, ProjectPhase WHERE COUNT( Employee ) = 1 ;

Should give you exactly what you wanted.
 

jsdba

Registered User.
Local time
Today, 08:25
Joined
Jun 25, 2014
Messages
164
First ProjectTeam shouldn't be a table in your database---I am hoping its a query. Because by your definition, employees are not assigned directly to a Project, but to a Phase of a project. Also, because you have multiple employees and they can be assigned to multiple phases in multiple roles that means at least another table to sort all that out.

So, since I don't have your complete table structure I can only give you general advice. You need 2 datasets:

A. A table/query of all project/phases.

B. A query of all project/phases with someone who has a backup role.

Then you take A and B and make a new query out of them using this SQL:

Code:
SELECT A.Project, A.Phase FROM A LEFT JOIN B ON B.Phase=A.Phase AND B.Project=A.Project WHERE B.Phase IS NULL

That will give you all the phases without a backup.
Interesting. i never thought about breaking up the table. This is not an actual table but i knew a good answer would point me in the right direction. Thanks a mil. But out of curiosity is there a way to do the above without breaking up the table? like using a nested query?
 

jsdba

Registered User.
Local time
Today, 08:25
Joined
Jun 25, 2014
Messages
164
You don't seem to care about WHO is assigned, only how many are assigned.

Code:
SELECT ProjectNo, ProjectPhase FROM ProjectTeam GROUP BY ProjectNo, ProjectPhase WHERE COUNT( Employee ) = 1 ;

Should give you exactly what you wanted.
I do care. I need to know where there is no backup
 

plog

Banishment Pending
Local time
Today, 07:25
Joined
May 11, 2011
Messages
10,916
But out of curiosity is there a way to do the above without breaking up the table? like using a nested query?

Those are 2 different issues. You should set up your tables properly. That process is called normalization (google that). You don't structure tables to accomodate queries down the line, you structure tables properly because that's how databases function best.

The method I gave you is essentially a "nested" query, you need to get your 2 datasets (A&B) and then build another query using them together. It is feasible to write that in one SQL object, but realy no purpose since Access allows you to easily work with queries as datasources in other queries.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 28, 2001
Messages
22,799
What I showed you would produce EXACTLY the results you asked for in your first post. You DON'T care WHO is assigned; you care HOW MANY are assigned, based on what you showed us that you wanted.
 

Users who are viewing this thread

Top Bottom