Leo De Bruyn Leo De Bruyn - 5 months ago 8
PHP Question

SELECT rows that are referenced less than x times in another table

In MySQL I have two tables, my reservable "weekend":

id bigint(20) unsigned,
label varchar(64),
date_start date,
max_attendees smallint(5) unsigned


And my attendees:

id bigint(20) unsigned,
name varchar(64),
email varchar(255),
weekend bigint(20) unsigned


I want to select all weekends that have attendees less than their max_attendees. This includes weekends that have 0 attendees.

Note: I also need to ignore weekend with id "1";

Currently, this works fine with PHP (I'm using Wordpress for mysql access), like so:

$weekends = $wpdb->get_results("SELECT * FROM $weekends_table
WHERE id <> 1", ARRAY_A);

$open_weekends = array();

foreach ($weekends as $weekend) {
$id = $weekend['id'];
$attendees = $wpdb->get_row("SELECT COUNT(id) as attendees
FROM $attendees_table
WHERE weekend = $id", ARRAY_A);

if ( $attendees['attendees'] < $weekend['max_attendees'] ) {
$weekend['attendees'] = $attendees['attendees'];
$open_weekends[] = $weekend;
}
}


Shouldn't I be able to do this in MySQL without the PHP? My knowledge of MySQL doesn't extend that far. Can you suggest a query?

Answer

use the HAVING clause

This is untested, so you may have to play with it, but here's the gist:

SELECT w.*, COUNT(a.name)
FROM weekend w
LEFT JOIN attendees a
  ON w.id = a.weekend
WHERE w.id <> 1
GROUP BY w.id
HAVING (COUNT(a.name) < w.max_attendees) OR (COUNT(a.name) IS NULL)