Problem in join between two tables

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

Hi All
I have two tables: 1.tblEmp(comntain employees) and 2.tblDep(contain departments)
tblEmp contain
id
name
depid columns
depid is foreign key to table tblEmp.

Now the problem is that i have to pick only those records from tblDep which has atleast 5 employees.

Thanks in advance

Posted 25-Jan-12 19:35pm

Sharma Richa

Add a Solution

Comments

Sharma Richa 31-Jul-12 9:18am

If you down voted question then you should have courage to say why ?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

graciax8 · Answer 1 · 2012-01-25T20:10:00

Solution 1

what database is this?
MySql or MSSQL?

Thanks.

Posted 25-Jan-12 20:10pm

graciax8

Comments

Sharma Richa 26-Jan-12 2:11am

database is MSSQL

graciax8 26-Jan-12 2:21am

try this one.

SELECT d.depID, count(e.ID)
FROM tblDep AS d
LEFT OUTER JOIN tblEmp AS e
GROUP BY e.depID
HAVING count(e.ID) > 5

Michel [mjbohn] 26-Jan-12 2:15am

Please use the "Have a Question or Comment?" widget for your questions.
You can also reply to comments.

Sharma Richa 26-Jan-12 2:52am

now problem is that i have to select employee name and other columns of both tables.

graciax8 26-Jan-12 3:44am

just add the column on the SELECT statement
SELECT column>, column2, column3 FROM ...

Scubapro · Answer 2 · 2012-01-25T20:14:00

Solution 2

I don't think you'll need a JOIN here. If I am interpreting correctly, your tblDep has a column with the number of employees (let's call it 'nremp') in department depid. So why not use:

SQL

select *
from tblEmp, tblDep
where tblEmp.depid = tblDep.depid
and tblDep.nremp >= 5
;

Posted 25-Jan-12 20:14pm

Scubapro

Comments

graciax8 26-Jan-12 2:33am

you just used a comma to join the tables.
it's the same.

Amir Mahfoozi · Answer 3 · 2012-01-25T20:39:00

Solution 3

Here it is :

SQL

select d.depid, count(*) cnt  from tblDep d inner join tblEmp e on d.depid = e.depid
group by d.depid
having count(*)>4

Posted 25-Jan-12 20:39pm

Amir Mahfoozi