Hi Bob. Thanks. I tried DISTINCT and GROUP BY but that didn't work. Yes,
there is one unique account number for each customer. See if this makes
more sense:
On the first ASP page I'm doing a select of a bunch of fields from the
CUSTOMER and ORDERS table joining on ACCTNO. Then I display a table that
show the number of order <30 Days Past Due, 30-60 Days Past Due etc... When
they click on "VIEW" (next to that column) I send the AcctList to the second
page that pulls up a list all of the customers.
So the first page might be:
<30 = 13 VIEW
30-60 = 4 VIEW
60 - 90 = 5 VIEW
The first select looks like this:
SELECT DISTINCT c.ACCTNO As Acct, s.SHIPDATE, DATEDIFF(day, s.SHIPDATE,
GETDATE()) AS DUEDAYS FROM s INNER JOIN ORDERS o ON s.ORDNO = o.ORDNO INNER
JOIN CUSTOMER c ON o.ACCTNO = c.ACCTNO WHERE o.orederrep = 'Matt' AND
o.oredertype = 'Video' ORDER BY DUEDAYS DESC
I then try and loop through to create a list of acct numbers to pass to the
next page
To create the list I was doing this:
do while not rs.eof
AcctList = AcctList & trim(rs("Acct")) & ","
rs.movenext
loop
In the above case there's only one customer - #53, but I get a list like
(53,53,53,53,53,53,53)
When they click on "View" I pass AcctList to the next page to fill the
SELECT statement, Ex:
sql = "SELECT * FROM CUSTOMER WHERE ACCTNO IN (" & AcctList & ") "
(No, I'm not using SELECT * I just didn't want to type all the field names
here).
The reason I wanted to use the IN statement is because all the second page
is doing is pulling back a list of all the customer names that met the
criteria from the page before. For example, all customers that are 30 days
past due.
Does that help explain it better?
Thanks!
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ui**************@TK2MSFTNGP11.phx.gbl...
David Berry wrote: Hi All. I'm trying to write an ASP page that shows me the UNIQUE
account number for a customer (so I can pass it to another page)
based on a search criteria. For example, I want to do a select
statement on the customer table and orders table (joining on the
ACCTNO) for all "Video" products.
Ex:
sq = "SELECT CUSTOMER.ACCTNO, CUSTOMER.NAME FROM CUSTOMER INNER JOIN
ORDERS ON CUSTOMER.ACCTNO = ORDERS.ACCTNO
WHERE PRODUCT_TYPE = 'Video' "
Once I get the result set back I want to get a list of the UNIQUE
account numbers I am assuming there is only one account number per customer. If I'm wrong,
this suggestion will need to be amended.
Use either GROUP BY, or the DISTINCT keyword in your query (please use
table aliases to make your queries more readable).
sq = "SELECT c.ACCTNO, c.NAME FROM CUSTOMER c INNER JOIN ORDERS oON
c.ACCTNO = o.ACCTNO WHERE PRODUCT_TYPE = 'Video' GROUP BY c.ACCTNO, c.NAME "
or
sq = "SELECT DISTINCT c.ACCTNO, c.NAME FROM CUSTOMER c INNER JOIN ORDERS o
ON c.ACCTNO = o.ACCTNO WHERE PRODUCT_TYPE = 'Video' "
to pass to a SQL Statement on the next page that would have a WHERE ACCTNO IN "( & var & ") " Ex:
sql = "SELECT * FROM CUSTOMER WHERE ACCTNO IN (" & AcctList & ") "
Don't use selstar. Always specify the fields you want to retrieve so ADO
does not have to make an extra trip to the database to resolve the * into
the actual field names.
I'm not clear on the need to do this. IN (<list>) is not very efficient.
It should be avoided when there is a more efficient method. In this case,
your first query already returns the list of account numbers you're interested
in. Why not just use the PRODUCT_TYPE criterion again to retrieve these
new results? It should be more efficient than using IN (<list>) ...
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"