SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code:
    	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
    This is for a product search on one of my websites. If you see the code above you'll see the following:

    Code:
    			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
    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:

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

  2. #2
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post a nicely formatted query first, get it working right and then ASPify it.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •