SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get result from 3 tables in a SINGLE query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

julyzerg
Starting Member

4 Posts

Posted - 06/17/2014 :  13:16:07  Show Profile  Reply with Quote
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?

Edited by - julyzerg on 06/17/2014 13:19:31

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 06/17/2014 :  13:20:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000