Problem SQL Code.
Posted on 2006-11-22
We have a website using SQL (And we are new to SQL in web sites......)
We wish to display a summary of items from a given database table. Our current code (THAT WORKS FINE) looks like this:
SQL="SELECT Distinct City FROM Listings ORDER BY City ASC"
do while not rs.eof
Total = conn.execute("SELECT count(City) FROM Listings WHERE City ='"&rs("City")&"'")%>
We have a variable in the table - STATUS - and this is used to determine if the property is ON or OFF the market - ON and you can view the details in the detail view - OFF - you know....
What we would like is for the 'display summary' to show only the properties that are ON the market - thereby our query needs to include an additional opperand.
we have tried the following:
Total = conn.execute("SELECT count(City) FROM Listings WHERE City ='"&rs("City")&"'") AND (Status="ON"))%>
but does not work.
Can anyone help us please.