Help me rewrite my ASP code to join tables in my DB :)

Hey everyone,

I have some code I need modified and I can't seem to figure it out. Here's the section of code I need to change:

	SELECT CASE True
	CASE Current_Category <> "" OR Request.Querystring("Search") <> "" OR Request.Querystring("mfg") <> ""
		'Display records from the chosen category...
		CurrentCat = Current_Category

		SQL = "SELECT " & Config_ProductsTable_Fields
		SQL = SQL & " FROM Products_Joined P " & SQL_LockType("NOLOCK")

		'-------------------------------------------------------------------------
		If Current_Category <> "" Then
			If RS_GetCatInfo.EOF Then
				SQL = SQL & " WHERE 1=2 "
			Elseif RS_GetCatInfo.Fields("Custom_Where_Clause") <> "" Then
				SQL = SQL & " WHERE " & AddSQLConstraints()
				SQL = SQL & " " & RS_GetCatInfo.Fields("Custom_Where_Clause") & " "
			Elseif RS_GetCatInfo.Fields("CategoryVisible") = "W" Then
				SQL = SQL & " WHERE " & AddSQLConstraints()
				'SELECT ALL NEW PRODUCTS ONLY...
				If (Config_DatabaseServer = "SQL") OR (Config_DatabaseServer = "MySQL") Then
					SQL = SQL & " DateAdd(DD," & Config_NewGraphicDays & ",P.DisplayBeginDate) > " & FormatDateConstant(LocalDateTime) & " "
				Else
					SQL = SQL & " DateAdd('D'," & Config_NewGraphicDays & ",P.DisplayBeginDate) > " & FormatDateConstant(LocalDateTime) & " "
				End If
			Else
				SQL = SQL & " WHERE p.ProductID IN ( "
				SQL = SQL & " SELECT p2.ProductID FROM Products p2 INNER JOIN Categories_Products_Link cpl ON cpl.ProductID = p2.ProductID WHERE " & Replace(AddSQLConstraints(),"P.","p2.") & " "

	
				Dim Refinement_CategoryIDs_Array, Refinement_Total_Cats, Refinement_CategoryIDs_CSV
				Refinement_CategoryIDs_Array = Split(Current_Category & Refinement_CategoryIDs,",")
				Refinement_Total_Cats = Ubound(Refinement_CategoryIDs_Array)+1
				Refinement_CategoryIDs_CSV = Current_Category & Refinement_CategoryIDs
				
				SQL = SQL & " AND cpl.CategoryID IN (" & Refinement_CategoryIDs_CSV & ")"
				SQL = SQL & " GROUP BY p2.ProductID "
				SQL = SQL & " HAVING Count(cpl.CategoryID) = " & Refinement_Total_Cats
				SQL = SQL & " ) "
			End If
		End If
	
		'-------------------------------------------------------------------------
		'If we haven't put the where clause in yet, do it now...
		If NOT InStr(SQL," WHERE ") > 0 Then
			SQL = SQL & " WHERE " & AddSQLConstraints()		
		End If
		
		'-------------------------------------------------------------------------			
		If Request.Querystring("Search") <> "" Then
			'Split search words into multiple words...
			SearchText = Trim(Replace(Request.Querystring("Search"), "'", ""))
			Split_SearchText = Split(SearchText," ")
			
			SQL = SQL & " AND "
			SQL = SQL & "( ("
			Call GenerateSearchQuery(Split_SearchText, "P.ProductCode")
			SQL = SQL & ") OR ("
			Call GenerateSearchQuery(Split_SearchText, "P.ProductName")
			SQL = SQL & ") OR ("
			Call GenerateSearchQuery(Split_SearchText, "p.ProductManufacturer")
			SQL = SQL & ") OR ("
			Call GenerateSearchQuery(Split_SearchText, "p.ProductKeywords")

			If Request.Querystring("Extensive_Search") = "Y" Then
				SQL = SQL & ") OR ("
				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescriptionShort")
				SQL = SQL & ") OR ("
				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescription")
			End If

			SQL = SQL & ") )"
		End If

Code (markup):

This is for a product search on one of my websites. If you see the code above you'll see the following:

			SQL = SQL & " AND "
			SQL = SQL & "( ("
			Call GenerateSearchQuery(Split_SearchText, "P.ProductCode")
			SQL = SQL & ") OR ("
			Call GenerateSearchQuery(Split_SearchText, "P.ProductName")
			SQL = SQL & ") OR ("
			Call GenerateSearchQuery(Split_SearchText, "p.ProductManufacturer")
			SQL = SQL & ") OR ("
			Call GenerateSearchQuery(Split_SearchText, "p.ProductKeywords")

			If Request.Querystring("Extensive_Search") = "Y" Then
				SQL = SQL & ") OR ("
				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescriptionShort")
				SQL = SQL & ") OR ("
				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescription")
			End If

Code (markup):

Basically... the script is checking the productcode field, productname, productmanufacturer, and productkeywords as part of the search. What I want to do is search the metatag_keywords (not the productkeywords). But here's the problem... metatag_keywords is not in the current table being used for this search already. So I need to open up a new table to search for information out of that table. I'm guessing using SQL Inner Join between 2 tables? The current script is joining 2 tables together using this code:

				SQL = SQL & " WHERE p.ProductID IN ( "
				SQL = SQL & " SELECT p2.ProductID FROM Products p2 INNER JOIN Categories_Products_Link cpl ON cpl.ProductID = p2.ProductID WHERE " & Replace(AddSQLConstraints(),"P.","p2.") & " "

Code (markup):

Now... what I need to do is something similar but instead of joining the Products table with the Categories_Products_Link table I need to join Products with the Products_Memos table. Inside the Products_Memos table I have a column named METATAG_Keywords that I want to run my search againast.

So......

Can someone help me get this working? I've tried modding the code around myself but everything I do seems to cause an error.

Idealy I could take the code above joining two tables together and joing a thrid table. But if I remember correctly, you can only join 2 tables at a time. What can I do to solve this problem and enable being able to search on another table in my database?

 

Please post a location where we could download the ASP and maybe a SQL script to rebuild the Tables with junk data?

Thanks... this will make it easier to help you out.

 

Hi ccoonen,

Well... I finally got this working... but I have a question. My original code before the change was:

SQL = SQL & " FROM Products_Joined P " & SQL_LockType("NOLOCK")

And my new code is:

SQL = SQL & " FROM Products_Joined P " & SQL_LockType("NOLOCK") & " INNER JOIN Products_Memos pm " & SQL_LockType("NOLOCK") & " ON p.ProductID = pm.ProductID "

I also found that this works as well:

SQL = SQL & " FROM Products_Joined P INNER JOIN Products_Memos pm ON p.ProductID = pm.ProductID "

So my question is... what is SQL_LockType("NOLOCK")? Do I need to use it in my code or not? I found that with or without it everything seems to work like I need it to. I just don't know wether it's a good idea to use it or not.

Does anyone know?

 

please upgrade to .net. thx.

 

LOL TasteOfPower: Not everyone has a .NET server available to them, hehe.

Truthfully MidoriWeb: I've never seen or used SQL_LockType("NOLOCK"). I've locked the tables with SQL while doing an update or insert before but never with an ASP command...

 

I've used NOLOCK quite a bit, although its never by choice as its widely regarded as a bad move.

Basically it tells SQL Server to ignore any locks on a row and just give you data, its commonly used because its faster than a normal select because SQL Server doesn't have the overhead of checking the row for locks.

It does have one major drawback however (although this is sometimes exactly why its used), it will see a uncommitted row change and return that data, IE you have started a transaction, updated a row but not yet committed the transaction, then you run a SELECT with NOLOCK you will see that uncommitted changed data where as normally the SELECT would wait until the transaction had been committed.

There are times of course where you don't really care about the state of the data however using NOLOCK is often the sign of a under maintained database which has deadlock problems, to get around the deadlocks people used NOLOCK to avoid the problem without fixing the underlying issue.

Jen

 

Hi Jen,

Thank you for the wonderful explanation. So the upside is it's one less step that's needed before SQL returns the information to the application (speeds things up). The downside is it will return a row that has not yet committed it's changes.

So if you're working on a row in a table, and update half of that information but haven't entered the 2nd half yet, and call that information using nolock, it will display only half the information. But, if you call that information using nolock it won't display anything because the data hasn't been committed yet.

Do I have that right?

Why then, is this regarded as a bad move? If I don't care about uncommitted data being shown, then this is one less step SQL needs to take and therefor it speeds everything up.

 

Almost

If you use NOLOCK you will see any data changed/updated thats in a transaction or generally locked.

If you dont use NOLOCK if there are any locks on the table it will wait for those locks to be released before reading the completed data.

Its a bit of an over simplification of it, as there are different levels of locking but its a good rule of thumb.

Generally you should let SQL Server manage itself, if you have to tell SQL Server to lock a certain way or use specifically a certain index you reduce SQL Servers ability to manage itself.

Specifically NOLOCK is seen as a bit of a hack as its often used to gloss over serious problems with the database performance that could by solved by proper indexing and tuning or improving the design of the DB.

There are times when NOLOCK or any other optimization hint is exactly the right thing to do, the trick is choosing those times carefully.

Jen

 

Gotcha,

Thanks again Jen... that all makes sense. 99% of the time our software does not use the nolock command when accessing SQL... so I'm guessing the developers knew what they were doing when they decided to use it on a select few calls to the DB.

Thanks again