Need to rewrite this query.... 1

[fergman]

SQL = "SELECT * FROM products WHERE department = '"& strDepartment &"' AND Course = '"& strCourse &"' AND [Section] = '"& strSection &"'"

Basically I have a large book database, that I need to query individual books out of it, it is formatted like so with each being it's own column:
department course section
ACCT 251 01
ACCT 251 01

If it sees multiple entries it is supposed to assume each one is a book for that class. That part works fine. the issue is resulting from the query finding results like this:
ART 251 12

notice the course matches the acct books, this results in the art course displaying in the drop down box, when chosen it of course doesn't display correct information.
how can I rewrite that query to account for thaT?

 

[denoxis]

When you use SELECT DISTINCT * , it will return the unique records. However, if the returned records have a unique ID (let say each book has the same name but different ISBN) then it will still return doubles. On your second query, you should go with that unique ID number, not with course

 

[fergman]

Ok, I can use isbn, as it is in this database, so how would I write this query?
the customer will choose the department, course, section, then I'm matching each of those to the equivalent fields in the database (I write their info to strings then compare it).
If someone chooses ACCT 251 01, and it has say 3 books, I don't understand how I would tell it to match the ISBN to each of the 3 matches, but avoid running into another department that has the same course

 

[denoxis]

If the first one returns ISBN, department, course and section your list box should have the ISBNs as the option value:
<SELECT NAME=COMBOBOX1>
<%while not rs.eof%>
<OPTION VALUE=&quot;<%=RS(&quot;ISBN&quot%>&quot;> blah blah
<%rs.movenext
loop%>
</SELECT>

When you submit, read the form input then do another query with this ISBN number:

SQL = &quot;SELECT * FROM book_detail WHERE ISBN = '&quot; & request.form(&quot;COMBOBOX1&quot & &quot;';&quot;

conn.execute(SQL)

something like that.

 

[fergman]

Unfortunatly that won't work, I don't have that information available to the customer for various reasons, all the customer sends me is the dep, course, section.
Perhaps a second query would still work though, if I queried just for the ID's of each item, (or isbn's I suppose), and stored them in an array, then the second query matched based on just their ID's. I'd probably have to nest the second one in the loop or something though, that could get messy.

 

[fergman]

I figureed it out, I just added another comparison in the query. based on the section and the course, it solved it.