creating a query between to linked tables

[ahd2008]

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

[ADezii]

Query With Linked Tables

Here is the SQL, but better yet, download the Attachment:

SELECT Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc, Count(Employees.Emp_ID) AS Total
FROM Departments INNER JOIN Employees ON Departments.Dep_ID = Employees.Dep_ID
GROUP BY Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc;

[ahd2008]

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(Serv ices). 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

[ADezii]

Query With Linked Tables

Not a Problem, just change from an Inner Join to a Left Outer Join, or download the Attachment for a visual:

SELECT Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc, Count(Employees.Emp_ID) AS Total
FROM Departments LEFT JOIN Employees ON Departments.Dep_ID = Employees.Dep_ID
GROUP BY Departments.Dep_ID, Departments.Dep_Name, Departments.Dep_Loc;

[ahd2008]

Thanks genius I really appreciate your help.

Good Luck

[ADezii]

Has nothing to do with genius, everything to do with a Left Outer Join ! (LOL)!