CODEWITHSUNDEEP
sqlsql-server-2008
user3591637
user3591637

Reputation: 499

How do you join tables sharing the same column?

I made an SQL Fiddle and what I would like to do is join these two queries by using the departmentid.

What I would like to show is the departmentname and not_approved_manager.

Would it be best to use a union or join in this case?

Tables

create table cserepux
(
   status int, 
   comment varchar(25),
   departmentid int,
   approveddate datetime 
);

insert into cserepux (status, comment, departmentid, approveddate)
values (1, 'testing1', 1, NULL), (1, 'testing2', 1, NULL),
       (1, 'testing2', 2, NULL), (0, 'testing2', 1, NULL),
       (0, 'tesitng2', 1, NULL), (0, 'testing2', 1, NULL),
       (0, 'tesitng2', 1, NULL), (0, 'testing3', 2, NULL),
       (0, 'testing3', 3, NULL);

create table cseDept 
(
    departmentid int, 
    department_name varchar(25)
);

insert into cseDept (departmentid,department_name)
values (1, 'department one'), (2, 'department two'),
       (3, 'department three'), (4, 'department four');

Query

select 
    departmentid, 
    COUNT(*) AS 'not_approved_manager'
from 
    cserepux
where 
    approveddate is null
group by 
    departmentid

SELECT * FROM cseDept

Upvotes: 0

Views: 48

Answers (2)

jean
jean

Reputation: 4350

Do you need just a join and a correct group by

  select dep.department_name, COUNT(*) AS 'not_approved_manager'
  from cseDept dep
  join cserepux cs on cs.departmentid = dep.departmentid
  where approveddate is null
  group by dep.department_name

Fiddle: http://sqlfiddle.com/#!3/5cf4e/30

Since joins and group by are really basic things in SQL I can suggest you do take a look on some tutorials to get a bit more proficiency whit it. You can try SQL Server Central stairway articles series

Upvotes: 1

Becuzz
Becuzz

Reputation: 6866

You need to do a join. A union will not get you what you want.

select d.department_name, COUNT(*) AS 'not_approved_manager'
from cserepux c
inner join cseDept d on c.departmentid = d.departmentid
where approveddate is null
group by d.department_name

Upvotes: 3

Related Questions