I have two tables as follows:

create table first (f_id int,rank varchar(5), primary key (f_id));
create table second(s_id int,assigned int,f_id int,primary key(s_id),foreign key(f_id) references first(f_id));  

Then I have inserted some records in these tables:

insert into first values (1,'A'),(2,'B'),(3,'C');
insert into second values(1,3,1),(2,5,2),(3,7,2),(4,1,2);

Now I want to select all records from these two tables where if first.f_id=second.f_id then it should count the assigned column's value, but if it does not match then it should return print 0.
My desired output would be:

rank         assigned
 A               1
 B               3
 C               0  

How can I achieve this?

2 Answers 2


You need an outer join for this:

select f.rank, count(s.s_id) as assigned
from first f
  left join second s on f.f_id = s.f_id
group by f.rank
order by f.rank;

It's important to count the rows from the outer joined table. A simple count(*) would return the wrong values. count() ignores null values and the rows for which no match exists in the second table will have a null value in s.s_id and thus these rows are counted with 0


You can achieve that by using a LEFT JOIN between first and second and using COUNT(assigned). Since assigned could be NULL in the LEFT JOIN your COUNT will return 0 as expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.