CODEWITHSUNDEEP
sql-server-2008
kevin n
kevin n

Reputation: 21

How can i join these two tables?

I'm having two tables. for example 1st table is the parent table, i.e department which contains deptno (primary key) and deptname, the other table is the child table, i.e employee which contains empid, empname, sal and deptno(foreign key). I want to join these two tables to get output like deptname and the number of employees working in that particular department. I tried the query using joins, views, cursors, sub-queries, group clause etc. I can able to get the solution for half of the query using group by clause, to find out the number of employees under particular department but can't figure it out the other half.

department

deptno    deptname
101       sales
102       hr
103       finance

employee

empid  empname   sal    deptno
  1     john    1500$    101
  2     kevin   1000$    101
  3     james   1200$    101
  4     ford    700$     102
  5     david   855$     102
  6     george  955$     103

I want to display the following table using an SQL query.

deptname    no.of employees
sales            3
hr               2
finance          1

Upvotes: 2

Views: 141

Answers (4)

codingbiz
codingbiz

Reputation: 26376

Hope this helps

Select deptname, count(1) no_employees
From table1 t1
Join table2 t2 on t1.deptno = t2.deptno
Group By deptname
Order By Count(1) desc

Upvotes: 1

Mike S.
Mike S.

Reputation: 4869

SELECT t1.deptname AS deptname, COUNT(1) AS "no. of employees" FROM table1 t1 JOIN table2 t2 ON t2.deptno = t1.deptno GROUP BY t1.deptno;

YOU can recreate this by performing the following:

mysql> 
mysql> CREATE TABLE table1 (deptno INT NOT NULL, deptname VARCHAR(24) NOT NULL, PRIMARY KEY (deptno));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE table2 (empid INT NOT NULL, empname VARCHAR(48) NOT NULL, sal VARCHAR(16), deptno INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO table1 VALUES (101, "sales"), (102, "hr"), (103, "finance");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO table2 VALUES (1, "John", "1500$", 101);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO table2 VALUES (2, "Kevin", "1200$", 101), (3, "James", "1000$", 101);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO table2 VALUES (4, "Ford", "700$", 102), (5, "David", "855$", 102), (6, "George", "955$", 103);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT t1.deptname AS deptname, COUNT(1) AS "no. of employees" FROM table1 t1 JOIN table2 t2 ON t2.deptno = t1.deptno GROUP BY t1.deptno;
+----------+------------------+
| deptname | no. of employees |
+----------+------------------+
| sales    |                3 |
| hr       |                2 |
| finance  |                1 |
+----------+------------------+
3 rows in set (0.00 sec)

mysql>

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this 

select d.deptname,COUNT(*) [no.of employees]
from employee E join department D
on E.deptno=D.deptno
group by d.deptname
order by COUNT(*)

Upvotes: 0

Ram Singh
Ram Singh

Reputation: 6918

Try the following code.

select COUNT(*) from tblEmp E inner join tblDept d on d.Deptid=l.Deptid

Hope this will solve your problem.

Upvotes: 0

Related Questions