Multiple queries?

[GGawaran]

I think this would be the proper title for the problem im currently having.

Just a jyst of what the problem im running into is....

1) This page was coded by yet again the same employee whom used to work here a few years back, with time, we need to update.

2) The problem is, we used to only ship via UPS, but now that we offer shipping through other means. However, the page I am working on only allows for orders with UPS tracking numbers to show.

Example

Order Number Status Tracking Number
9999999 Shipped 1ZXXXXXXXXXX
9999998 Shipped


From the top we see that it will show the UPS but not the USPS order. I changed the code up and was able to switch it around vice versa and was dumbfounded as to why it wouldnt show both.

Now that ive confirmed that both items are in two different tables of the db I can figure out how to code it so that I can access both tables, while retaining information from both. (Am I even making any sense?)

Heres a portion of the code.

		i = 0
	
		if not rstemp.eof then
			
			set rs = createobject("adodb.recordset")
		
			do until rstemp.eof OR howmanyrecs>=maxrecs

				invoice_id = rstemp("unique_key")
				invoice_status = rstemp("invoice_status")

				
				invoice_tracking = ""
				
								
				set rs = createobject("adodb.recordset")
				rs.open "select * from invoices where invoice_tracking = '" & rstemp("invoice_tracking") & "'",strDSN,3,1
				
				
				if not rs.eof then		
					if isnull(rs("invoice_tracking")) or rs("invoice_tracking") = null then
						invoice_tracking = "NO TRACKING # YET"
					else
						invoice_tracking = rs("invoice_tracking")
					end if
				end if
				
				
				x_1 = x_1 + 1

				row_name = "abc" & trim(rstemp("invoice_tracking")) & "_" & x_1
				row_name = replace(row_name," ","")
				row_name = replace(row_name,".","")
				row_name = replace(row_name,"'","")
				row_name = replace(row_name,"@","")
				row_name = replace(row_name,"?","")
				row_name = replace(row_name,"%","")
				row_name = replace(row_name,"!","")
				row_name = replace(row_name,"#","")
				row_name = replace(row_name,"$","")
				row_name = replace(row_name,"^","")
				row_name = replace(row_name,"*","")
				row_name = replace(row_name,"(","")
				row_name = replace(row_name,")","")
				row_name = row_name & "_" & i				

			 	i = i + 1
				if i mod 2 = 0 then
					response.write "<tr bgcolor='#cccccc'>" 
				else
					response.write "<tr bgcolor='#ffffff'>"
				end if
				
				if instr(invoice_tracking,"1Z5354RV") then
					invoice_tracking = "<a href=pages_x_orders_tracking.asp?rid=" & invoice_tracking & ">" & invoice_tracking & "</a>"
				end if
				
				if instr(invoice_tracking,"1Z2Y3656") then
					invoice_tracking = "<a href=pages_x_orders_tracking.asp?rid=" & invoice_tracking & ">" & invoice_tracking & "</a>"
				end if
				
				if instr(invoice_tracking,"9101") then
				invoice_tracking = "<a href=pages_x_orders_tracking.asp?rid=" & invoice_tracking & ">" & invoice_tracking & "</a>"
				end if
				
				response.write "<font size='2'>"
				response.write "<td valign='top'><font face='Arial' size='2'>" & invoice_id & "</font></td>"
                response.write "<td valign='top'><font face='Arial' size='2'>" & invoice_status & "</font></td>"
                response.write "<td valign='top'><font face='Arial' size='2'>" & invoice_tracking & "</font></td>"
                response.write "</font></tr>"

Thanks in advance everyone,

Gregory Gawaran

[iam_clint]

I wouldn't have used two different tables


but you can join the tables togethor in a query

select * from tracking t
left outer join other_tracking o on t.column = o.column
where yadda = 1
order by date

to join them togethor where i put t.column = o.column there has to be something to match one table to the other like user_Id or something so it would be like on t.user_id = o.user_id

[GGawaran]

so instead of this

				set rs = createobject("adodb.recordset")
				rs.open "select * from invoices where invoice_tracking = '" & rstemp("invoice_tracking") & "'",strDSN,3,1

use this?

select * from tracking t(t being the element in the table tracking?)
left outer join other_tracking(which would be the other table?) o on t.column = o.column
where yadda = 1 (im abit confused by this piece of code)
order by date

Thanks,

Gregory Gawaran

[GGawaran]

this is how i interpretted what you said to do, but, am not sure if its right, well everything but the yadda part haha.

				set rs = createobject("adodb.recordset")
				rs.open "select * from tracking tracking_number
				left outer join invoices invoice_tracking on tracking_number.column = invoice_tracking.column
				where yadda = 1
				order by date" 
				& rstemp("invoice_tracking") & "'",strDSN,3,1

thanks,

Gregory Gawaran

[iam_clint]

column actually needs to be a column name, one where it = the other

[GGawaran]

ah alrighty, as for yadda, what goes there?

[GGawaran]

I was having a problem with unterminated string, but i have fixed that problem (i think?) and now the error im getting is ""Microsoft OLE DB Provider for ODBC Drivers error '80040e21' ODBC driver does not support the requested properties."" its saying the error is on 184, which is where this string of code sits.

This is the code I have for the combine, but I don't know if its correct and is causing the problem but here is the code.

				set rs = createobject("adodb.recordset")
				rs.open "select * from tracking tracking_number	left outer join invoices invoice_tracking on tracking.tracking_number = invoices.invoice_tracking where yadda = 1 order by date" & rstemp("invoice_tracking") & "'",strDSN,3,1

Im assuming that for the column =, the first part is the table, and the second after the . is the column within the table?

Thanks again in advance,

Gregory Gawaran

[iam_clint]

yes

rs.open "select * from tracking tracking_number	left outer join invoices invoice_tracking on tracking.tracking_number = invoices.invoice_tracking order by date",strDSN,3,1

[GGawaran]

yes

rs.open "select * from tracking tracking_number	left outer join invoices invoice_tracking on tracking.tracking_number = invoices.invoice_tracking order by date",strDSN,3,1

thanks alot clint, learned something new yet again thanks to you guys.

Gregory Gawaran

PS

Keep up the great help you guys offer us newbies.