SQLTeam.com | Weblogs | Forums

, determine the number of jobs posted. If a company doesn't have posted jobs, show 0 for that company

determine the number of jobs company posted. If a company doesn't have posted jobs, show 0 for that company.*

Provide sample table sample columns and sample data

hi

i tried to do this !!!
hope this is correct and it helps :slight_smile: :slight_smile:

please click arrow to the left for drop create sample data
drop table #company 
go 

create table #company
(
Company_name varchar(100)
)
go 

insert into #company select 'Virtusa'
insert into #company select 'Infosys'
insert into #company select 'Hewlett Packard'
insert into #company select 'Maxim'
go 

select 'data from table company',* from #company
go 


drop table #jobs_posted
go 

create table #jobs_posted
(
Company_name varchar(100),
Jobs_posted int 
)
go 

insert into #jobs_posted select 'Virtusa' ,10 
insert into #jobs_posted select 'Infosys',80
go 

select 'data from table jobs posted',* from #jobs_posted
go

image

please click arrow to the left for SQL
SELECT 'SQL OutPut', 
       a.company_name, 
       CASE 
         WHEN b.company_name IS NULL THEN 0 
         ELSE b.jobs_posted 
       END 
FROM   #company a 
       LEFT JOIN #jobs_posted b 
              ON a.company_name = b.company_name

image