Repeating display data... 2

[A1Pat]

Hello,
This calling of mine keep displaying a result, which I know for sure only 1 data, again and again and again for pages. Could someone tell me where I went wrong here:

mySQL = "SELECT b.idOrder " _
& "FROM customer a, cartHead b " _
& "WHERE a.idCust = b.idCust " _
& " AND a.tempCust = 'Y'"
set rs = openRSexecute(mySQL)
do while not rs.eof
response.write rs("idOrder") & "<BR>"
loop
call closeRS(rs)
response.End()

 

[lespaul]

your problem is probably caused by:

[tt]FROM Customer a, cartHead b[/tt]

this is a cartesian join and basically multiples all the records from Customer by all the records in cartHead.

This may work:

mySQL = "SELECT b.idOrder " _
          & "FROM   customer a " _
          & "INNER JOIN CartHead b ON a.idCust = b.idCust " _
          & "WHERE  a.tempCust = 'Y'"
    set rs = openRSexecute(mySQL)
    do while not rs.eof
        response.write rs("idOrder") & "<BR>"
    loop
    call closeRS(rs)
    response.End()

or if you only want a SINGLE idOrder for each, add DISTINCT:

mySQL = "SELECT DISTINCT b.idOrder " _
          & "FROM   customer a " _
          & "INNER JOIN CartHead b ON a.idCust = b.idCust " _
          & "WHERE  a.tempCust = 'Y'"
    set rs = openRSexecute(mySQL)
    do while not rs.eof
        response.write rs("idOrder") & "<BR>"
    loop
    call closeRS(rs)
    response.End()

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[A1Pat]

First, thank you Leslie for your input.

I'd already tried using Inner Join but since I realize there's only 1 idOrder for every idCust; hence, I changed the code to this one.

However, to make sure, I tried again with the code given and the result is still exactly the same.

:-(

 

[lespaul]

can you provide some sample data from the two tables and your expected results from the sample?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[traingamer]

try

    do while not rs.eof
        response.write rs("idOrder") & "<BR>"
      [b]rs.movenext[/b]
    loop

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lespaul]

good catch on the missing next!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[A1Pat]

hahahah... you right Leslie, Greg catch the good one. My BAD!!!

Thank you both!!!