Mess of a Query - Please help!

Hi. I'm trying to determine the number of unfilled jobs. An unfilled
job in one where the number of people needed for that job is greate
than the number of people assigned to that job. The table "jobs" has
information about that job, including a userID associated with an
employer, and a value NumPeopleNeeded which says how many people that
employer needs for that job. The table "job_assignment" has two
columns, JobID (associated with the job listing in table "jobs") and
UserID associated with an employee ID. I may have a job where I need
4 people and I have assigned 2 employees so far. So there would be 2
entries in "job_assignment" to reflect those two assigned employees.
This is my query to get the # of jobs that are unfilled:

select count(j.JobID) from jobs j left join job_assignment k on
(j.JobID = k.JobID) where j.userID='13' and
count(k.JobID)<j.NumPeopleNeeded;

mysql is telling me that it's an invalid use of a group function. any
ideas?

Try out something like this...

SELECT noofjobs-(SELECT count(*) from job_assignment as a WHERE
a.job_id=b.jobid) FROM jobs as b GROUP BY b.jobid

On Apr 26, 5:48 am, "lindsayta...@gmail.com" <lindsayta...@gmail.com>
wrote:
> Hi. I'm trying to determine the number of unfilled jobs. An unfilled
> job in one where the number of people needed for that job is greate
> than the number of people assigned to that job. The table "jobs" has
> information about that job, including a userID associated with an
> employer, and a value NumPeopleNeeded which says how many people that
> employer needs for that job. The table "job_assignment" has two
> columns, JobID (associated with the job listing in table "jobs") and
> UserID associated with an employee ID. I may have a job where I need
> 4 people and I have assigned 2 employees so far. So there would be 2
> entries in "job_assignment" to reflect those two assigned employees.
> This is my query to get the # of jobs that are unfilled:
>
> select count(j.JobID) from jobs j left join job_assignment k on
> (j.JobID = k.JobID) where j.userID='13' and
> count(k.JobID)<j.NumPeopleNeeded;
>
> mysql is telling me that it's an invalid use of a group function. any
> ideas?

Try out something like this...

SELECT noofjobs-(SELECT count(*) from job_assignment as a WHERE
a.job_id=b.jobid) FROM jobs as b GROUP BY b.jobid