by I am editing pages created from Site Server 3, I'm also using SQL Server 6.5
I have a department page that I want to give the total products per
department. The problem I'm having is the page runs a query on a table to
list all of the departments based on product id. I want to execute two more
queries for each department in the loop. That's because the information I
need to display is in the products table, but to get the row from the
product table I need the sku number of the product. The sku number is in
another table associated with the product id's.
The original code is:
Function showItems(dept_id)
sqlText =
Replace(MSCSQueryMap.sub_departments.SQLCommand,":1",dept_id)
Set rsSubDepts = MSCS.Execute (sqlText, nSubDepts, adCmdText)
REM -- each level is either a list of subdepts or products
if Not rsSubDepts.EOF then
REM -- show subdepts
%>
<p><font SIZE="2">Select a Department:</font> <%
%> <ul>
<%
set dept_idField = rsSubDepts("dept_id")
set dept_nameField = rsSubDepts("dept_name")
do while Not rsSubDepts.EOF
%>
<li><a
HREF="<%= baseURL("dept.asp") & mscsPage.URLShopperArgs("dept_id" ,
dept_idField.value) %>"><%= dept_nameField.value %></a>
<%
rsSubDepts.MoveNext
loop
rsSubDepts.Close
%> </li>
the sub_departments query in the the first line queries the dept table. This
gives an id, name, and description.
The loop displays the results in a bulleted list, for each row in the query.
For each dept I need to query the dept_prod table to get the multiple sku
#'s that have the same dept_id value. In that query I need the amount of
sku's with that dept_id value(I assume I would use the COUNT function in the
SQL statement). The kicker is I also need the average price of all the
products in that department too. That would mean an array(I guess) to hold
the amounts, and the number of sku's so that I can calculate an average.
Right?
I don't know how to query more than one table at a time, and I would really
like to use the avg function in a SQL statement to calculate that average
cost for me.
This wouldn't be that hard if the product table kept the dept_id values for
each product!!!
As you can probably tell I don't know much of what I'm doing.
Any help would be appreciated.
Thanks.