Click here to Skip to main content
15,029,128 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi,

Kindly help me with a query

tbl-A

jobid, employerid, jobtitle, description, skills, status


tbl_b

Applicationid, jobid, candidateid,Appliedon status

I want the result to be as below

Jobid, employerid, jobtitle, description, skills, status, Applicants

Applicants column would be total count from tbl_b group by jobid

Thanks in advance

kind regards
Posted
Comments
ZurdoDev 5-Apr-12 10:55am
   
So, do it in SQL instead of jQuery.
Ahamed Azeem 5-Apr-12 11:05am
   
yup I need it in sql using Join not in JQuery

What about this SQL statement...

SELECT a.jobid, employerid, jobtitle, description, skills, status, COUNT(b.jobid) FROM tbl-A a JOIN tbl_b b ON a.jobid = b.jobid GROUP BY a.jobid

I tried it on a different table and it seemed to work ok.

Edit:
Changed the first one to a.jobid
   
v2
Comments
Ahamed Azeem 5-Apr-12 11:15am
   
showing error Ambiguous column jobid
SQL
select b.jobid,count(b.jobid) as ApplicantsCount,a.* from

tbl_job a inner join tbl_applicants b

on a.jobid=b.jobid

where a.Employerid=@Employerid

group by a.jobid,b.jobid,a.EmployerId,a.CreatedUserId,a.jobtitle,a.categoryid,a.description,a.skills,a.contactperson,a.contactnumber,a.createddate,a.expiredon,a.status
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900