Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get result from 3 tables in a SINGLE query

Author  Topic 

julyzerg
Starting Member

4 Posts

Posted - 2014-06-17 : 13:16:07
I am using 3 tables: projects, project_manager and project_employee

projects

- project_id (int, PK)
- project_name

project_manager

- project_id (int, PK)
- manager_id (int, PK)

project_employee

- project_id (int, PK)
- employee_id (int, PK)

Assume that a manager is currently logged in (so we know what is his ID), what I trying to do is write a query that will display a grid-view showing:

project_id, project_name and number of employees in project.

I tried these quires to test:

SELECT project_id, count(emp_id) as NumberOfEmployees into #tempTable FROM project_employee WHERE project_id IN (SELECT project_id FrOM projects) GROUP BY project_id

SELECT projects.project_id, projects.project_name, NumberOfEmployees FROM projects, #tempTable WHERE projects.project_id = #tempTable.project_id

Problems are:
1 - I used two queries not one
2 - The result is for all projects of all managers, I just want to display projects for a specific manager (for example mag_id = 5)
3 - it created a temporary table - which I dont want.

Is there anyway to get what I want in a single query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-17 : 13:20:23
You need to JOIN the tables together. Here's an example with 3 tables:

select t1.column1, t1.column2, t2.columnD, t3.columnZZ
from table1 t1
join table2 t2 on t1.column1 = t2.column1
join table3 t3 on t2.columnA = t3.columnA
where t1.column9 = 'SQLTeam'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -