Finding Unique Account Numbers

[David Berry]

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 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 & ") "

What I was trying to do (in the first result set) was something like this:

do while not rs.eof
AcctList = AcctList & trim(rs("ACCTNO ")) & ","
rs.movenext
loop

AcctList = Left(AcctList, len(AcctList) - 1)

The problem is that the list I get back looks like this:

(42,42,42,42,31 ,31,56,56,56,56 ,56,56,70,77,77 ,77 ....... etc )

How do I get just ONE account number for the search criteria?

Thanks

[Bob Barrows]

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"

[David Berry]

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******@NOyah oo.SPAMcom> wrote in message
news:ui******** ******@TK2MSFTN GP11.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"

[Bob Barrows]

David Berry wrote:

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 & ") "
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?

Sorry, but no. I don't understand why you don't use the ">30" (for example)
criterion to get the results for the second page.

A picture is worth a thousand words. Could you show a few rows of sample
data in tabular format for the CUSTOMERS and ORDERS tables. Just the minimum
columns to illustrate the problem. Then show the results you want to show on
each page.

Also tell us what database you are using.

--
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"