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.
| Author |
Topic |
|
julyzerg
Starting Member
4 Posts |
 Posted - 2014-06-17 : 13:16:07
|
| I am using 3 tables: projects, project_manager and project_employeeprojects - project_id (int, PK) - project_nameproject_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_idSELECT projects.project_id, projects.project_name, NumberOfEmployees FROM projects, #tempTable WHERE projects.project_id = #tempTable.project_idProblems are:1 - I used two queries not one2 - 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.columnZZfrom table1 t1join table2 t2 on t1.column1 = t2.column1join table3 t3 on t2.columnA = t3.columnAwhere t1.column9 = 'SQLTeam'Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
Get result from 3 tables in a SINGLE query