Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: query help

  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query help

    I need a query to return a list of courses which have not reached the enrollment capacity. So it will need to count the number of user/course pairs in student with a given courseid and select the fullname of courses that have not reached their capacity.

    students.userid
    students.courseid

    course.id
    course.fullname
    course.capacity

    query = SELECT fullname FROM course WHERE - The sum of students.userid with course.id < course.capacity

    Can anybody help?

  • #2
    New Coder
    Join Date
    Aug 2005
    Location
    Groningen, Netherlands
    Posts
    57
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Thumbs up

    Hi jwoker,

    i needed a query that looks like yours. I handled it with the following:

    Code:
    SELECT `courseid` AS cid, COUNT(*) AS cnt FROM `students` GROUP BY `courseid` HAVING cnt > (SELECT `capacity` FROM `course` WHERE `id` = cid)
    I think that should do the trick.

    Cheers,

    Jaap
    Last edited by jaap; 08-19-2005 at 12:57 PM.

  • #3
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried your query but I got error 1164 check syntax near - 'Select `capacity...'

  • #4
    New Coder
    Join Date
    Aug 2005
    Location
    Groningen, Netherlands
    Posts
    57
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by jwoker
    I tried your query but I got error 1164 check syntax near - 'Select `capacity...'
    is your table called exactly 'course' and your column exactly 'capacity' ?

    maybe you can try it without the ``. Furthermore, it is important you use the ` instead of the '.

    and what is your MySQL version?


  •  

    Posting Permissions

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