Professional Web Applications Themes

Mess of a Query - Please help! - MySQL

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 ...

  1. #1

    Default 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?

    lindsaytabas@gmail.com Guest

  2. #2

    Default Re: Mess of a Query - Please help!

    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

    Mitul Guest

  3. #3

    Default Re: Mess of a Query - Please help!

    On Apr 26, 5:48 am, "com" <com>
    wrote: 

    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

    Mitul Guest

Similar Threads

  1. Firefox CSS mess
    By patrikz in forum Macromedia Dynamic HTML
    Replies: 3
    Last Post: January 5th, 11:03 PM
  2. I'm in a right mess
    By davecheet in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 5
    Last Post: April 27th, 08:29 AM
  3. Help! I'm a Newbie Get me out of this mess!
    By The_FedEx_Guy in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 6th, 12:48 PM
  4. Need help getting out of a potential mess
    By redsmartie@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 8
    Last Post: August 3rd, 05:15 PM
  5. h2xs mess
    By Sisyphus in forum PERL Modules
    Replies: 3
    Last Post: July 9th, 10:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139