So it should look like this:

SELECT p2.ProductID  
 FROM Products p2 INNER JOIN Categories_Products_Link cpl  
  ON cpl.ProductID = p2.ProductID 
 INNER JOIN Products_Memos pm  
  ON pm.ProductID = p2.ProductID 

Thanks for the help :)

So I tried the following code but it's not working... what am I doing wrong?

*****************************************

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.") & " INNER JOIN Products_Memos pm ON pm.ProductID = p2.ProductID"

JAG

************************

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'INNER'.

SearchResults_preload.asp, line 573

***************************

Line 573 in that file is the following:

****************************

RS_Search.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly

I I edited my code to the following and everything worked without any errors:

*************************
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 INNER JOIN Products_Memos pm ON pm.ProductID = p2.ProductID WHERE " & Replace(AddSQLConstraints(),"P.","p2.") & " "
*************************

However... now that I'm trying to call the column from the new table it's causing an error. The error I'm getting is the following:

*************************
Microsoft OLE DB Provider for SQL Server error '80040e14'

The column prefix 'pm' does not match with a table name or alias name used in the query.

/SearchResults_preload.asp, line 573
*************************

The code I've changed is from this:

Call GenerateSearchQuery(Split_SearchText, "p.ProductKeywords")

to this:

Call GenerateSearchQuery(Split_SearchText, "pm.METATAG_Keywords")

That caused the error I posted above. I then tried to change my original code of joining tables in the "AddSQLContraints" section from this:

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

To this:

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

Doing so didn't cause an error by changing only that... but when I changed the code that called from the pm.METATAG_Keywords table it did throw an error.

Any ideas?

If it's in Products_Joined, you will need to say P.METATAG_Keywords. If not, then you need to do the JOIN in the first FROM clause after Products_Joined P.

I changed:

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

To:

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

And got this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'WITH'.

/store/incpages/SearchResults_preload.asp, line 573

Any ideas?

I changed this line:

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

To this:

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

I am then able to call my METATAG_Keywords column from the Products_Memos table.

But... I have one last question... What does SQL_LockType("NOLOCK") do at the end of my original code? I had to remove it to get my new code working... but I can't figure out what that little piece of code actually does.

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

Is there a reason I should or shouldn't be using the SQL_LockType?