By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

creating a query between to linked tables

P: 68
Hi

I have similar to the following tables

1) Departments

Dep_ID,Dep_name,Dep_Loc

2) Employees

Emp_ID,Emp_Name,Dep_ID

the first table includes these records

Dep_ID Dep_name Dep_Loc
------------------------
10 Finance USA
20 Drilling USA

while the otehr table

Emp_ID Emp_Name Dep_ID
----------------------------------------
222309 Johan 10
222338 Steve 10
223443 Culen 20
233456 Martin 20
233456 Clap 20

the question now is how to create a query which count
all employees in each department. I want the query result in:

Dep_ID Dep_name Dep_Loc Total
------------------------------
10 Finance USA 2
20 Drilling USA 3
Nov 27 '08 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,597
Here is the SQL, but better yet, download the Attachment:
Expand|Select|Wrap|Line Numbers
  1. SELECT Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc, Count(Employees.Emp_ID) AS Total
  2. FROM Departments INNER JOIN Employees ON Departments.Dep_ID = Employees.Dep_ID
  3. GROUP BY Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc;
Nov 28 '08 #2

P: 68
Thanks ADezii for your assistance

This is what I wanted but I still having a miner problem.Let's assume that I added a new department

Dep_ID Dep_name Dep_Loc
---------------------------------------
10 Finance USA
20 Drilling USA
30 Services USA

And I dindn't add any employee to this department(Services). Now I want this query to display this department and show the total=0 :

Dep_ID Dep_name Dep_Loc Total
-------------------------------------------------
10 Finance USA 2
20 Drilling USA 3
30 Services USA 0
Nov 28 '08 #3

ADezii
Expert 5K+
P: 8,597
Not a Problem, just change from an Inner Join to a Left Outer Join, or download the Attachment for a visual:
Expand|Select|Wrap|Line Numbers
  1. SELECT Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc, Count(Employees.Emp_ID) AS Total
  2. FROM Departments LEFT JOIN Employees ON Departments.Dep_ID = Employees.Dep_ID
  3. GROUP BY Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc;
Nov 28 '08 #4

P: 68
Thanks genius I really appreciate your help.

Good Luck
Nov 28 '08 #5

ADezii
Expert 5K+
P: 8,597
Has nothing to do with genius, everything to do with a Left Outer Join ! (LOL)!
Nov 30 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.