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

[jsdba]

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

 

[plog]

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:

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]

You don't seem to care about WHO is assigned, only how many are assigned.

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

Should give you exactly what you wanted.

 

[jsdba]

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:

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]

You don't seem to care about WHO is assigned, only how many are assigned.

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]

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]

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.