Filter content of dymanic drop-down list

[Biglop]

O.K., I've got two tables, seminars and enrollments. I've got a dynamic select list, populated by a simple query on the seminars table:

<CFQUERY DATASOURCE="Seminars" NAME="dates_1">
SELECT seminar_id, session_number, seminar_date, seminar_time, seminar_site
FROM seminars
WHERE session_number = 1
ORDER BY session_number, seminar_date, seminar_site
</CFQUERY>

This is used for students to choose from seminars. Max class size is 13, so i dont want a class to appear in the list if it already has 13 participants.

I made a query to give me number of occurences of a seminar ID in the enrollment table:

<CFQUERY DATASOURCE="Seminars" NAME="seminar_full">
SELECT Seminars.seminar_id, Count(Enrollments.Seminar) AS occurences
FROM Seminars INNER JOIN Enrollments ON Seminars.seminar_id = Enrollments.Seminar
GROUP BY Seminars.seminar_id
</CFQUERY>

Now for the problem... How do I write the cfselect to only show me the seminars I want (those with less than 13 participants??

Thanks. --Steve

 

[FALCONSEYE]

SELECT Seminars.seminar_id, Count(Enrollments.Seminar) AS occurences
FROM Seminars INNER JOIN Enrollments ON Seminars.seminar_id = Enrollments.Seminar
GROUP BY Seminars.seminar_id
having count(enrollments.seminar) <14

http://www.jetansolutions.com

 

[Biglop]

Sorry, I should have been more clear...how do I populate the drop-down list, so it only shows the seminars with <13 participants?

You gave me a clue though, so now I have a query with all of seminars i don't want to display:

QUERY 2:

SELECT Seminars.seminar_id, Count(Enrollments.Seminar) AS occurences
FROM Seminars INNER JOIN Enrollments ON Seminars.seminar_id = Enrollments.Seminar
GROUP BY Seminars.seminar_id
having count(enrollments.seminar) <14

Now I need to populate my drop-down list with this query:

QUERY 1:

<CFQUERY DATASOURCE="Seminars" NAME="dates_1">
SELECT seminar_id, session_number, seminar_date, seminar_time, seminar_site
FROM seminars
WHERE session_number = 1
ORDER BY session_number, seminar_date, seminar_site
</CFQUERY>

and filter-out the ones appearing in the results of QUERY 2 ... but I don't know how

 

[FALCONSEYE]

the query 2 only includes the seminars with 13 or less participants. so, you will just display that query result in your second related select. you will want to include the seminar name in your select and group statement and then something like


<select name="selectSeminar">
<cfloop query="seminar_full">
<option value="#seminar_id#">#seminarName#</option>
</cfloop>
</select>

hope it helps...

http://www.jetansolutions.com

 

[FALCONSEYE]

sorry, either


<cfoutput>
<select name="selectSeminar">
<c<select name="selectSeminar">
<cfloop query="seminar_full">
<option value="#seminar_id#">#seminarName#</option>
</cfloop>
</select>

or

<select name="selectSeminar">
<cfoutput query="seminar_full">
<option value="#seminar_id#">#seminarName#</option>
</cfoutput>
</select>

http://www.jetansolutions.com