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?