CODEWITHSUNDEEP
sql
user3591637
user3591637

Reputation: 499

How to get all names of a table column to show if its not part of another table?

I have a table that has the names of the department, but if a department is not part of the count in the other table then I don't get the department name.

How can I get the names of the departments who are not part of the CSEReduxResponses to display in the output with a 'branchtotalstarsgiven' of 0?

Here are some test data and the query that gets me the count of the departments. 

create table CSEReduxDepts (csedept_id int, csedept_name varchar(25));

insert into CSEReduxDepts (csedept_id, csedept_name)
values (1,'one'),
(2,'two'),
(3,'three'),
(4,'four');

create table CSEReduxResponses (execoffice_status int, submitterdept int);

insert into CSEReduxResponses (execoffice_status,submitterdept)
values (1,1),
(1,1),
(1,1),
(1,1),
(1,2),
(1,2);

------------------------------------------------------------------
SELECT  submitterdept,csedept_name, COUNT(*) as 'branchtotalstarsgiven'

FROM CSEReduxResponses c
join CSEReduxDepts  d on
c.submitterdept= d.csedept_id
WHERE execoffice_status = 1

GROUP BY execoffice_status, submitterdept, csedept_name

Upvotes: 0

Views: 85

Answers (3)

Serpiton
Serpiton

Reputation: 3684

To get the expected result there are few changes to implement:

  • change the JOIN from INNER to LEFT/RIGHT
  • change the WHERE condition so that it will not remove the department not in CSEReduxResponses
  • change the COUNT so that it'll return 0 when the department is not in CSEReduxResponses

the resulting query is 

SELECT submitterdept
     , csedept_name
     , COUNT(execoffice_status) as 'branchtotalstarsgiven'
FROM   CSEReduxDepts d
       LEFT JOIN CSEReduxResponses c on d.csedept_id = c.submitterdept
WHERE  COALESCE(execoffice_status, 1) = 1
GROUP BY execoffice_status, submitterdept, csedept_name

SQLFiddle demo

the changed part are in bold, the COUNT use execoffice_status as parameter because COUNT(*) count every row, but COUNT(field) count only the rows that are not null.

Upvotes: 1

Blood-HaZaRd
Blood-HaZaRd

Reputation: 2124

AS I understood you need names of departements that don't figure in the second table : 

SELECT csedept_id, csedept_name FROM CSEReduxDepts
WHERE csedept_id NOT IN (SELECT distinct submitterdept FROM CSEReduxResponses)

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Changing your inner join to an outer join should work. Something like this: 

from CSEReduxDepts d left join SEReduxResponses c 
on c.submitterdept= d.csedept_id 
and execoffice_status = 1

Upvotes: 0

Related Questions