#1
  1. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144

    selecting NOT...?


    Hi...I'm really trying not to be a help vampire, but I'm really stuck here.

    Trying to display a list of classes a client can schedule with a few restrictions:

    - display all classes the client is eligible for
    - display the number of attendees for each class
    - don't display the class if the client has already scheduled it

    I have all of this except the last one. My query:

    Code:
    SELECT DATE_FORMAT(classes.date_time, '%W, %M %e') AS day, DATE_FORMAT(classes.date_time, '%l:%i %p') AS time, classes.status, classes.class_name, classes.min_capacity, classes.max_capacity, classes.pkey, (SELECT COUNT(*) FROM scheduled_classes WHERE scheduled_classes.classes_key=classes.pkey) AS attendees FROM classes, scheduled_classes WHERE (classes.class_type='floor' OR classes.class_type='apparatus') AND DATE_FORMAT(classes.date_time, '%Y-%m')='2014-11' AND classes.date_time >= NOW() GROUP BY classes.pkey
    My tables:

    classes:

    pkey.....class_type.....min_capacity.....max_capacity.....date_time

    1..........floor...............3........................11........................11/1/14
    2..........floor...............3........................11........................11/2/14
    3..........private...........1.........................1.........................11/5/14


    clients:

    pkey.....name

    1..........homer simpson
    2..........mr ed
    3..........steve harris


    scheduled classes:

    pkey.....client_key.....class_key

    1...........1...................2


    My query above is giving me everything I need, but I'd also like to DEselect the classes where the client is already scheduled. It wouldn't be too hard to write some PHP code fu to manage this, but I prefer to streamline the query if it's possible.

    Thoughts?

    Thank you!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    Do a LEFT JOIN against the scheduled classes list, with a matching client_key and class_key, and then restrict the results WHERE the pkey IS NULL.
  4. #3
  5. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144
    Originally Posted by requinix
    Do a LEFT JOIN against the scheduled classes list, with a matching client_key and class_key, and then restrict the results WHERE the pkey IS NULL.
    Clearly, I'm not doing this right:

    SELECT DATE_FORMAT(classes.date_time, '%W, %M %e') AS day, DATE_FORMAT(classes.date_time, '%l:%i %p') AS time, classes.status, classes.class_name, classes.min_capacity, classes.max_capacity, classes.pkey, (SELECT COUNT(*) FROM scheduled_classes WHERE scheduled_classes.classes_key=classes.pkey) AS attendees FROM classes, scheduled_classes LEFT JOIN clients ON scheduled_classes.client_key=clients.pkey WHERE clients.pkey=NULL AND (classes.class_type='floor' OR classes.class_type='apparatus') AND DATE_FORMAT(classes.date_time, '%Y-%m')='2014-11' AND classes.date_time >= NOW() GROUP BY classes.pkey
    This returns 0 results. It should be closer to 90.

    I'm sorry I'm not getting this. It's been a marathon project and I'm very close to collapse.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    You're LEFT JOINing against clients...
    Code:
    SELECT
    	DATE_FORMAT(classes.date_time, '%W, %M %e') AS day,
    	DATE_FORMAT(classes.date_time, '%l:%i %p') AS time,
    	classes.status,
    	classes.class_name,
    	classes.min_capacity,
    	classes.max_capacity,
    	classes.pkey,
    	(SELECT COUNT(*) FROM scheduled_classes WHERE scheduled_classes.classes_key=classes.pkey) AS attendees
    FROM classes
    LEFT JOIN scheduled_classes ON classes.pkey=scheduled_classes.classes_key AND client_key=<value>
    WHERE
    	(classes.class_type='floor' OR classes.class_type='apparatus')
    	AND DATE_FORMAT(classes.date_time, '%Y-%m')='2014-11'
    	AND classes.date_time >= NOW()
    	AND scheduled_classes.classes_key IS NULL
    GROUP BY classes.pkey
    (which includes removing a reference to scheduled_classes that wasn't being used)

    Comments on this post

    • Frank Grimes agrees : Simple. Brilliant. I owe you a pint.
  8. #5
  9. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144
    Originally Posted by requinix
    You're LEFT JOINing against clients...
    Code:
    SELECT
    	DATE_FORMAT(classes.date_time, '%W, %M %e') AS day,
    	DATE_FORMAT(classes.date_time, '%l:%i %p') AS time,
    	classes.status,
    	classes.class_name,
    	classes.min_capacity,
    	classes.max_capacity,
    	classes.pkey,
    	(SELECT COUNT(*) FROM scheduled_classes WHERE scheduled_classes.classes_key=classes.pkey) AS attendees
    FROM classes
    LEFT JOIN scheduled_classes ON classes.pkey=scheduled_classes.classes_key AND client_key=<value>
    WHERE
    	(classes.class_type='floor' OR classes.class_type='apparatus')
    	AND DATE_FORMAT(classes.date_time, '%Y-%m')='2014-11'
    	AND classes.date_time >= NOW()
    	AND scheduled_classes.classes_key IS NULL
    GROUP BY classes.pkey
    (which includes removing a reference to scheduled_classes that wasn't being used)


    Oh, man. I stared at that for an hour. Really, I can't thank you enough! This works beautifully.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    two corrections...

    first, for the sake of efficiency, replace these two lines --
    Code:
    AND DATE_FORMAT(classes.date_time, '%Y-%m')='2014-11'
    AND classes.date_time >= NOW()
    with these two lines --
    Code:
    AND classes.date_time >= CURRENT_DATE
    AND classes.date_time  < '2014-12-01'
    you can replace the hardcoded date for the first of the next month with an actual calculation based on CURRENT_DATE if you wish

    secondly, a more interesting problem is the join to scheduled_classes

    you have this --
    Code:
    LEFT JOIN scheduled_classes ON classes.pkey=scheduled_classes.classes_key
    but then you also have
    Code:
    AND scheduled_classes.classes_key IS NULL
    which, together, are the classic technique to return only those classes rows (the left table) which ~don't~ have any matching rows in the scheduled_classes table

    so that makes the subquery in the SELECT clause always return 0, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    Originally Posted by r937
    secondly, a more interesting problem is the join to scheduled_classes

    you have this --
    Code:
    LEFT JOIN scheduled_classes ON classes.pkey=scheduled_classes.classes_key
    but then you also have
    Code:
    AND scheduled_classes.classes_key IS NULL
    which, together, are the classic technique to return only those classes rows (the left table) which ~don't~ have any matching rows in the scheduled_classes table

    so that makes the subquery in the SELECT clause always return 0, right?
    The JOIN also has "AND client_key=<value>". So the JOIN counts only the scheduling for that class and that user (ie, user already joined the class), while the subquery counts the scheduling for that class and any user.


    Hmm. Seems I didn't include the table name in that condition.
    Code:
    LEFT JOIN scheduled_classes ON classes.pkey=scheduled_classes.classes_key AND scheduled_classes.client_key=<value>

IMN logo majestic logo threadwatch logo seochat tools logo