Well that was just to get your attention. Here's the thing: I need to write a query which creates a recordset, of available lectures, with a field with the available spaces left in the lecture room.

I want to list the lectures and say that the capacity is 'X' (label.qty)from table "A" then count the registrants from table "B" who have selected that class. Subtract the count from the capacity to display the available spaces.

Alt. 1 if the count is >= capacity the lecture is not included in the recordset.



Here is table "A":
CREATE TABLE `label` (
`id_lbl` int(3) unsigned NOT NULL auto_increment,
`descr_lbl` varchar(80) default '',
`group_lbl` tinyint(3) unsigned default NULL,
`order_lbl` tinyint(3) unsigned default NULL,
`cost` decimal(8,2) default NULL,
`qty` int(50) default NULL, ----capacity of class
PRIMARY KEY (`id_lbl`)
) ENGINE=MyISAM AUTO_INCREMENT=71 DEFAULT CHARSET=latin1


Table "B" is a record of each registrant which has a field for classes holding the "id_lbl" of the class they have selected.


Oldnickj

I guess this is a sub-query thing but I can't quite visualize it.

any ideas (r937)!

yes, certainly

what's with the _lbl lable lables?

you may need an extra "u" being way North...

We also spell "the" "teh" usually.

Anyway.. so for each class record in the label table I need to count the instances of the label.id_lbl in the registration table then subtract that from the label.qty, return the sum and return nothing if a 0.

oldnickj

let's make this a two-step solution

teh first step is to write a simple GROUP BY query on the registration table by itself, to get a count for each lbl_id, oops i mean id_lbl, in that table

Select count(breChoice_reg)
FROM Meet_reg
group by breChoice_reg

close, but it's off in a couple of ways

first of all, by selecting only the count, you're going to get a result set that looks like this --so just having the counts by themselves is not very useful, because you can't tell which label each count belongs to

secondly, GROUP BY breChoice_reg would give you counts for each breChoice_reg, rather than for each class/lecture, which would be id_lbl instead

(aside: why would you call a class or lecture a "label"? people's creativity never ceases to amaze me)

so what you were looking for was this --please confirm that this works and gives the correct results, before we go on to the second of our two steps

the label table is a look up table.

SELECT breChoice_reg
, COUNT(*) AS registrations
FROM Meet_reg
GROUP
BY breChoice_reg

result:
62 3
63 2
65 1
66 2

this is the correct result. "breChoice" holds the id of the name of the lecture (they call them classes as in school classes but I decided not not use that word because I'm CSS sensitive!

the next step is simple -- just join to this subquery with a LEFT OUTER JOIN (outer instead of inner because you want to allow for lectures which have no registrations at all yet)
i shall leave it to you to figure out what goes in the SELECT clause

note that when a subquery is used like this, it's called a derived table, and you can think of the result set of the subquery as an actual table (which it is)

So spring a new one on me...it works and is so simple it's going to take a while to understand why it works!

thanks

Works great in an editor but not on the web page. "standby" is not being echoed perhaps because it is producing NULL when I try to subtract registrations from qty and there are no reservations. see image!





Here is my new query:

SELECT descr_lbl, qty
, registrations
, qty-registrations AS standby
FROM label
LEFT OUTER
JOIN ( SELECT breChoice_reg
COUNT(*) AS registrations
FROM Meet_reg
GROUP BY breChoice_reg ) AS c
ON c.breChoice_reg = label.id_lbl
WHERE label.qty >= COALESCE(c.registrations,0)
AND group_lbl = 7

Problem was in the PHP now it works very well

thanks!

you have the right idea but i would clean up the SELECT clause with table qualification as follows: