I have tested this thing pretty much every way I can and even waited for my client to try and break it. And it's still working for this particular setup. I can include other parts here if needed but I want to at least post back with the JOIN I am using. It has a couple of additions but rather than me trying to strip it down to bare minimum and risk a typo, here it is. I'll include an explanation from my point of view in case it's read by someone else learning this stuff like me. It still uses a slightly modified version of your subquery in the last suggestion you gave. (thanks)
Of course, feel free to correct me if anything is wrong because I know that even if it is working, that doesn't mean it is correct. And the last thing I want to do is mislead someone trying to learn joins.
Again, this is used to populate a dropdown select in a form used to reserve tables at an event. I needed the event dates to show up in the dropdown list only if the following were met.
a). The event had to be in the future. b). At least 5 tables still available. c). The logged in user had not already reserved that date.
SELECT B.booth_id, date_format(show_date, '%M %e, %Y') as show_date, R.UserID, COALESCE(sum(reserved), 0) AS total, capacity - COALESCE(sum(reserved), 0) AS available
FROM tblbooths AS B LEFT JOIN tblreserved AS R ON R.booth_id=B.booth_id
WHERE B.booth_id NOT IN (SELECT booth_id FROM tblreserved WHERE UserID = ParamUserID)AND show_date >= CURDATE()
GROUP BY show_date
HAVING available > 5
- Using LEFT JOIN so that all records from the table on the left, "tblbooths" are returned even if the table on the right does not have a record that matches.
- The "B" and "R" used is shorthand. So B.booth_id is from the FROM tblbooths AS B.
- COALESCE(sum(reserved), 0) AS total = This takes the total (sum) of the column 'reserved' and returns it as a new name "total". But if the column is NULL, returns a "0" for that record. Not sure it's required to do it that way, but it works.
- capacity - COALESCE(sum(reserved), 0) AS available = This one first looks at the 'capacity' column, then SUBTRACTS the SUM of 'reserved' and returns that number as "available".
- show_date >= CURDATE() returns all dates if they are from today forward. So even if an event date was not sold out, but is a past date, it disappears from the reservation form's select.
- The WHERE is then making sure that a subquery is compared to the first SELECT and that no show dates that were containing the logged in user were included in the query. This was the trickiest part of all.
- GROUP BY show_date = This is a consolidation or "aggregate function". Basically, as I understand it, the show_date column is grouped at it's top level so that rather than showing the same event date several times in the list, it groups each date.
- HAVING available > 5 = This one is looking for events that have more than 5 tables available so as to prevent overselling. And it works. But I'm a little unsure if this fits the standard because I have read that the HAVING column must be included in the GROUP BY. Maybe that was a misunderstanding on my part but I read that after I had this already working.