Simple Sql Concept, clarification required

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

Simple Sql Concept, clarification required

Author

Topic

dinujohn
Starting Member

2 Posts

Posted - 2010-11-02 : 06:02:51

I have two tables

Employee
---------
empID, empName, add, deptID

primary key is empID. deptID is foreign key.

Department
-----------
deptID, deptName, desc

primary key is deptID.

Now i want to get the count of number of employees for a department.

The following are the possible queries:

1. select count(*) as empCount from Employee e, Department d where d.deptID=<deptID> and d.deptID = e.deptID

2. select count(*) as empCount from Employee e where e.deptID = <depID>

which is the best query to find the count ?

The first query has a check if the department exist. If the count is zero what is the inference ? Department does not exist or there are no employees in the department.

The second query doesn't check if department exist. A department can exist without employees. There is no way to know department exist.

What is the ideal way to query if i am told to get count of employees for a department ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-02 : 06:53:45

This is an example, because you haven't told us about RI.

SELECT		d.deptName,
		COUNT(e.deptID) AS Employees
FROM		dbo.Department AS d
LEFT JOIN	dbo.Employee AS e ON e.deptID = d.deptID
GROUP BY	d.deptName

N 56°04'39.26"
E 12°55'05.63"

dinujohn
Starting Member

2 Posts

Posted - 2010-11-02 : 07:25:42

I want the count for a particular deptID, i.e employees belonging to a particular department.

How do we proceed in this case ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-02 : 07:54:33

Did you try my suggestion?

SELECT		d.deptName,
		COUNT(e.deptID) AS Employees
FROM		dbo.Department AS d
WHERE		d.deptID = 1
LEFT JOIN	dbo.Employee AS e ON e.deptID = d.deptID
GROUP BY	d.deptName

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources