SQL statement Count

Forum|Forum|19 years ago
June 7, 2006
4 replies
359 views

DW8, classic asp,vbscript

I need help with a sql statement . I am trying to fill a dynamic dropdown
list with values from my db but I only need to display the records where the
count for the timeID field is less than 15 for a particular date.

Fields are: scheduleID, dateID, timeID, firstName, lastName.

When the user selects a date, I only want the times to display where there
are less than 15 students signed up for the class. If the class is full (15
students), then that time will not display in the time dropdown.

Any suggestions on how to do this?

thx, donna

Server side applications

This topic has been closed for replies.

N

Newsgroup_UserAuthor

Inspiring

This seems to be for a single class, so:

SELECT D.classDate, T.classTime
FROM (classTimes T
CROSS JOIN classData D)
LEFT OUTER JOIN classSchedules ON D.dateID=S.dateID AND
T.timeID=S.classTimes
GROUP BY D.classDate, T.classTime
HAVING COUNT(S.ScheduleID) < 15
ORDER BY D.classDate, T.classTime

"Donna Lucas" <dlucas@wilsonnc.org> wrote in message
news:e67cc8$cl3$1@forums.macromedia.com...
> ok, I'm confused. Does the info below help you to explain it to me...
>
> My main table name is classSchedules. This is where I want to insert the
> new registration.
> The other tables are classDates and classTimes.
>
> classSchedules fields
> scheduleID - key field for the classSchedules table.
> dateID - foreign key for the classDates id
> classTimes - foreign key for the classTimes id
> firstName - text field for users first name
> lastName - text field for users last name
>
> classTimes fields
> timeID - autonumber
> classTime - text
>
> classData fields
> dateID - autonumber
> classDate - text
>
>
>
>
>
>
> "Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
> news:e675dd$44o$1@forums.macromedia.com...
>> You didn't provide enough information about your structure, but a typical
>> solution would be:
>>
>> SELECT C.ClassID, C.ClassName, C.Time
>> FROM Classes C
>> LEFT OUTER JOIN Schedules S ON C.ClassID=S.ClassID
>> GROUP BY C.ClassID, C.ClassName, C.Time
>> HAVING COUNT(S.StudentID) < 15
>>
>> "Donna Lucas" <dlucas@wilsonnc.org> wrote in message
>> news:e66v24$pct$1@forums.macromedia.com...
>>> DW8, classic asp,vbscript
>>>
>>> I need help with a sql statement . I am trying to fill a dynamic
>>> dropdown list with values from my db but I only need to display the
>>> records where the count for the timeID field is less than 15 for a
>>> particular date.
>>>
>>> Fields are: scheduleID, dateID, timeID, firstName, lastName.
>>>
>>> When the user selects a date, I only want the times to display where
>>> there are less than 15 students signed up for the class. If the class
>>> is full (15 students), then that time will not display in the time
>>> dropdown.
>>>
>>> Any suggestions on how to do this?
>>>
>>> thx, donna
>>>
>>>
>>>
>>
>>
>
>

N

Newsgroup_UserAuthor

Inspiring

Here is the page I'm working on.... Thanks for your help!!!

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>


<%
Dim rsClass
Dim rsClass_numRows

Set rsClass = Server.CreateObject("ADODB.Recordset")
rsClass.ActiveConnection = MM_connSchedule_STRING
rsClass.Source = "SELECT * FROM classDates"
rsClass.CursorType = 0
rsClass.CursorLocation = 2
rsClass.LockType = 1
rsClass.Open()

rsClass_numRows = 0
%>
<%
Dim rsTimes
Dim rsTimes_numRows

Set rsTimes = Server.CreateObject("ADODB.Recordset")
rsTimes.ActiveConnection = MM_connSchedule_STRING
rsTimes.Source = "?????????????????????????????????????????????????"
rsTimes.CursorType = 0
rsTimes.CursorLocation = 2
rsTimes.LockType = 1
rsTimes.Open()

rsTimes_numRows = 0
%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
" http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns=" http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<p>Phone Class Registration</p>
<p> </p>
<p>Select your class day. </p>

<form id="form1" name="form1" method="POST" action="">
<table width="500" border="0">
<tr>
<td>First Name:
<input name="fName" type="text" id="fName" />
Last Name:
<input name="lName" type="text" id="lName" /></td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td><select name="selDate" id="selDate">
<option value="n">Select Class Date</option>
<%
While (NOT rsClass.EOF)
%>
<option
value="<%=(rsClass.Fields.Item("dateID").Value)%>"><%=(rsClass.Fields.Item("classDate").Value)%></option>
<%
rsClass.MoveNext()
Wend
If (rsClass.CursorType > 0) Then
rsClass.MoveFirst
Else
rsClass.Requery
End If
%>
</select></td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td><select name="selTime" id="selTime">
<option value="n">Select Class Time</option>
<%
While (NOT rsDisplayTimes.EOF)
%>
<option value="<%=(rsTimes.Fields.Item("**********DISPLAY THE TEXT FOR
THE DATE HERE(ie 6/26/2006*************)></option>
<%
rsDisplayTimes.MoveNext()
Wend
If (rsDisplayTimes.CursorType > 0) Then
rsDisplayTimes.MoveFirst
Else
rsDisplayTimes.Requery
End If
%>
</select> </td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td><input name="btnSubmit" type="submit" id="btnSubmit" value="Submit"
/> <input name="btnReset" type="reset" id="btnReset" value="Reset" /></td>
</tr>
</table>

</form>

</body>
</html>

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e675dd$44o$1@forums.macromedia.com...
> You didn't provide enough information about your structure, but a typical
> solution would be:
>
> SELECT C.ClassID, C.ClassName, C.Time
> FROM Classes C
> LEFT OUTER JOIN Schedules S ON C.ClassID=S.ClassID
> GROUP BY C.ClassID, C.ClassName, C.Time
> HAVING COUNT(S.StudentID) < 15
>
> "Donna Lucas" <dlucas@wilsonnc.org> wrote in message
> news:e66v24$pct$1@forums.macromedia.com...
>> DW8, classic asp,vbscript
>>
>> I need help with a sql statement . I am trying to fill a dynamic
>> dropdown list with values from my db but I only need to display the
>> records where the count for the timeID field is less than 15 for a
>> particular date.
>>
>> Fields are: scheduleID, dateID, timeID, firstName, lastName.
>>
>> When the user selects a date, I only want the times to display where
>> there are less than 15 students signed up for the class. If the class is
>> full (15 students), then that time will not display in the time dropdown.
>>
>> Any suggestions on how to do this?
>>
>> thx, donna
>>
>>
>>
>
>

N

Newsgroup_UserAuthor

Inspiring

ok, I'm confused. Does the info below help you to explain it to me...

My main table name is classSchedules. This is where I want to insert the
new registration.
The other tables are classDates and classTimes.

classSchedules fields
scheduleID - key field for the classSchedules table.
dateID - foreign key for the classDates id
classTimes - foreign key for the classTimes id
firstName - text field for users first name
lastName - text field for users last name

classTimes fields
timeID - autonumber
classTime - text

classData fields
dateID - autonumber
classDate - text

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e675dd$44o$1@forums.macromedia.com...
> You didn't provide enough information about your structure, but a typical
> solution would be:
>
> SELECT C.ClassID, C.ClassName, C.Time
> FROM Classes C
> LEFT OUTER JOIN Schedules S ON C.ClassID=S.ClassID
> GROUP BY C.ClassID, C.ClassName, C.Time
> HAVING COUNT(S.StudentID) < 15
>
> "Donna Lucas" <dlucas@wilsonnc.org> wrote in message
> news:e66v24$pct$1@forums.macromedia.com...
>> DW8, classic asp,vbscript
>>
>> I need help with a sql statement . I am trying to fill a dynamic
>> dropdown list with values from my db but I only need to display the
>> records where the count for the timeID field is less than 15 for a
>> particular date.
>>
>> Fields are: scheduleID, dateID, timeID, firstName, lastName.
>>
>> When the user selects a date, I only want the times to display where
>> there are less than 15 students signed up for the class. If the class is
>> full (15 students), then that time will not display in the time dropdown.
>>
>> Any suggestions on how to do this?
>>
>> thx, donna
>>
>>
>>
>
>

N

Newsgroup_UserAuthor

Inspiring

You didn't provide enough information about your structure, but a typical
solution would be:

SELECT C.ClassID, C.ClassName, C.Time
FROM Classes C
LEFT OUTER JOIN Schedules S ON C.ClassID=S.ClassID
GROUP BY C.ClassID, C.ClassName, C.Time
HAVING COUNT(S.StudentID) < 15

"Donna Lucas" <dlucas@wilsonnc.org> wrote in message
news:e66v24$pct$1@forums.macromedia.com...
> DW8, classic asp,vbscript
>
> I need help with a sql statement . I am trying to fill a dynamic dropdown
> list with values from my db but I only need to display the records where
> the count for the timeID field is less than 15 for a particular date.
>
> Fields are: scheduleID, dateID, timeID, firstName, lastName.
>
> When the user selects a date, I only want the times to display where there
> are less than 15 students signed up for the class. If the class is full
> (15 students), then that time will not display in the time dropdown.
>
> Any suggestions on how to do this?
>
> thx, donna
>
>
>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded