Adding new Table to Query

[Andrew1986]

First off my query

Code: Select all

strQuery = LimitSQL("tblCactuShop" & TABLE_PREFIX & "Products.P_ID, tblCactuShop" & TABLE_PREFIX & "Versions.V_Price, tblCactuShop" & TABLE_PREFIX & "Products.P_name" & numLanguageID & ", P_strapline" & CInt(numLanguageID) & " FROM (((tblCactuShop" & TABLE_PREFIX & "Versions INNER JOIN tblCactuShop" & TABLE_PREFIX & "Products ON tblCactuShop" & TABLE_PREFIX & "Versions.V_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink ON tblCactuShop" & TABLE_PREFIX & "Products.P_ID= tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Categories ON tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_CategoryID = tblCactuShop" & TABLE_PREFIX & "Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "Suppliers ON tblCactuShop" & TABLE_PREFIX & "Products.P_SupplierID = tblCactuShop" & TABLE_PREFIX & "Suppliers.SUP_ID WHERE P_Name" & CInt(numLanguageID)  & " <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0," & DB_CG_ID & ") AND P_CustomerGroupID IN (0," & DB_CG_ID & ") AND CAT_CustomerGroupID IN (0," & DB_CG_ID & ") AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=1 ORDER BY P_ID DESC", numNewestClearanceList, true)

that allows me to pull data from the Products and the Versions table in my Database but for some reason i cant get it to allow me to pull data from the ProductStats table in the data base where would i add in the query code to allow me to pull from the ProductSatats Table aswell as the other 2 mentioned.

[Rob]

Code: Select all

strQuery = LimitSQL("tblCactuShop" & TABLE_PREFIX & "ProductStats.FieldYouNeed, tblCactuShop" & TABLE_PREFIX & "Products.P_ID, tblCactuShop" & TABLE_PREFIX & "Versions.V_Price, tblCactuShop" & TABLE_PREFIX & "Products.P_name" & numLanguageID & ", P_strapline" & CInt(numLanguageID) & " FROM (((tblCactuShop" & TABLE_PREFIX & "Versions INNER JOIN tblCactuShop" & TABLE_PREFIX & "Products ON tblCactuShop" & TABLE_PREFIX & "Versions.V_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink ON tblCactuShop" & TABLE_PREFIX & "Products.P_ID= tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Categories ON tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_CategoryID = tblCactuShop" & TABLE_PREFIX & "Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "Suppliers ON tblCactuShop" & TABLE_PREFIX & "Products.P_SupplierID = tblCactuShop" & TABLE_PREFIX & "Suppliers.SUP_ID WHERE P_Name" & CInt(numLanguageID)  & " <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0," & DB_CG_ID & ") AND P_CustomerGroupID IN (0," & DB_CG_ID & ") AND CAT_CustomerGroupID IN (0," & DB_CG_ID & ") AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=1 ORDER BY P_ID DESC", numNewestClearanceList, true)

[Rob]

I injected the code into the first part of the statement...

Having worked on the cactushop product for cactusoft you could say I know the code inside out!

[Andrew1986]

adding that gave me this error



An error occurred executing the following query, and page execution has been terminated.
SELECT DISTINCT TOP 30 tblCactuShop1ProductStats.PS_DATE, tblCactuShop1Products.P_ID, tblCactuShop1Versions.V_Price, tblCactuShop1Products.P_name1, P_strapline1 FROM (((tblCactuShop1Versions INNER JOIN tblCactuShop1Products ON tblCactuShop1Versions.V_ProductID = tblCactuShop1Products.P_ID) INNER JOIN tblCactuShop1ProductCategoryLink ON tblCactuShop1Products.P_ID= tblCactuShop1ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop1Categories ON tblCactuShop1ProductCategoryLink.PCAT_CategoryID = tblCactuShop1Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop1Suppliers ON tblCactuShop1Products.P_SupplierID = tblCactuShop1Suppliers.SUP_ID WHERE P_Name1 <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0,0) AND P_CustomerGroupID IN (0,0) AND CAT_CustomerGroupID IN (0,0) AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=1 ORDER BY P_ID DESC
Error Description : No value given for one or more required parameters.
Page of error : /xxx/clearance.asp
Query String :

[Rob]

lol.... you need a join.... one sec

[Rob]

ok... as i dont have cactushop in front of me, can you please tell me all of the fields you need from the productStats table, along with all id cross reference fields in that table?

[Andrew1986]

Feilds = PD_ID , PS_ProductID, PS_Date, PS_IP

i need the data from PS_Date

[Rob]

This is awfully difficult to do without cactushop handy, but I think this should do it

Code: Select all

strQuery = LimitSQL("tblCactuShop" & TABLE_PREFIX & "ProductStats.PS_Date, tblCactuShop" & TABLE_PREFIX & "Products.P_ID, tblCactuShop" & TABLE_PREFIX & "Versions.V_Price, tblCactuShop" & TABLE_PREFIX & "Products.P_name" & numLanguageID & ", P_strapline" & CInt(numLanguageID) & " FROM ((((tblCactuShop" & TABLE_PREFIX & "Versions INNER JOIN tblCactuShop" & TABLE_PREFIX & "Products ON tblCactuShop" & TABLE_PREFIX & "Versions.V_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactusShop" & TABLE_PREFIX & "ProductStats ON tblCactusShop" & TABLE_PREFIX & "ProductStats.ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink ON tblCactuShop" & TABLE_PREFIX & "Products.P_ID= tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Categories ON tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_CategoryID = tblCactuShop" & TABLE_PREFIX & "Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "Suppliers ON tblCactuShop" & TABLE_PREFIX & "Products.P_SupplierID = tblCactuShop" & TABLE_PREFIX & "Suppliers.SUP_ID WHERE P_Name" & CInt(numLanguageID)  & " <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0," & DB_CG_ID & ") AND P_CustomerGroupID IN (0," & DB_CG_ID & ") AND CAT_CustomerGroupID IN (0," & DB_CG_ID & ") AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=1 ORDER BY P_ID DESC", numNewestClearanceList, true)

[Rob]

Oops... just spotted a bug... refresh the above post

[Andrew1986]

Now i get this



An error occurred executing the following query, and page execution has been terminated.
SELECT DISTINCT TOP 30 tblCactuShop1ProductStats.PS_Date, tblCactuShop1Products.P_ID, tblCactuShop1Versions.V_Price, tblCactuShop1Products.P_name1, P_strapline1 FROM ((((tblCactuShop1Versions INNER JOIN tblCactuShop1Products ON tblCactuShop1Versions.V_ProductID = tblCactuShop1Products.P_ID) INNER JOIN tblCactusShop1ProductStats ON tblCactusShop1ProductStats.Product_ID = tblCactuShop1Products.P_ID) INNER JOIN tblCactuShop1ProductCategoryLink ON tblCactuShop1Products.P_ID= tblCactuShop1ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop1Categories ON tblCactuShop1ProductCategoryLink.PCAT_CategoryID = tblCactuShop1Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop1Suppliers ON tblCactuShop1Products.P_SupplierID = tblCactuShop1Suppliers.SUP_ID WHERE P_Name1 <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0,0) AND P_CustomerGroupID IN (0,0) AND CAT_CustomerGroupID IN (0,0) AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=1 ORDER BY P_ID DESC
Error Description : The Microsoft Jet database engine cannot find the input table or query 'tblCactusShop1ProductStats'. Make sure it exists and that its name is spelled correctly.
Page of error : /heinniem/clearance.asp
Query String :


but the table is there

[Rob]

You may want to check that table name... like I said, this is all from memory

[Rob]

Spotted it... I used CactusShop instead of CactuShop

Please amend and retry

[Andrew1986]

you are being a great help but now i get


An error occurred executing the following query, and page execution has been terminated.
SELECT DISTINCT TOP 30 tblCactuShop1ProductStats.PS_Date, tblCactuShop1Products.P_ID, tblCactuShop1Versions.V_Price, tblCactuShop1Products.P_name1, P_strapline1 FROM ((((tblCactuShop1Versions INNER JOIN tblCactuShop1Products ON tblCactuShop1Versions.V_ProductID = tblCactuShop1Products.P_ID) INNER JOIN tblCactuShop1ProductStats ON tblCactuShop1ProductStats.Product_ID = tblCactuShop1Products.P_ID) INNER JOIN tblCactuShop1ProductCategoryLink ON tblCactuShop1Products.P_ID= tblCactuShop1ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop1Categories ON tblCactuShop1ProductCategoryLink.PCAT_CategoryID = tblCactuShop1Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop1Suppliers ON tblCactuShop1Products.P_SupplierID = tblCactuShop1Suppliers.SUP_ID WHERE P_Name1 <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0,0) AND P_CustomerGroupID IN (0,0) AND CAT_CustomerGroupID IN (0,0) AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=1 ORDER BY P_ID DESC
Error Description : No value given for one or more required parameters.
Page of error : /heinniem/clearance.asp
Query String :

[Rob]

This is doing my head in.... give me a while to troubleshoot this..

[Andrew1986]

take all the tme you need as you are being greatful enough to help

[Rob]

Try

Code: Select all

strQuery = LimitSQL("tblCactuShop" & TABLE_PREFIX & "ProductStats.PS_Date, tblCactuShop" & TABLE_PREFIX & "Products.P_ID, tblCactuShop" & TABLE_PREFIX & "Versions.V_Price, tblCactuShop" & TABLE_PREFIX & "Products.P_name" & numLanguageID & ", P_strapline" & CInt(numLanguageID) & " FROM ((((tblCactuShop" & TABLE_PREFIX & "Versions INNER JOIN tblCactuShop" & TABLE_PREFIX & "Products ON tblCactuShop" & TABLE_PREFIX & "Versions.V_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink ON tblCactuShop" & TABLE_PREFIX & "Products.P_ID= tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Categories ON tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_CategoryID = tblCactuShop" & TABLE_PREFIX & "Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "Suppliers ON tblCactuShop" & TABLE_PREFIX & "Products.P_SupplierID = tblCactuShop" & TABLE_PREFIX & "Suppliers.SUP_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "ProductStats ON tblCactuShop" & TABLE_PREFIX & "ProductStats.PS_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID WHERE P_Name" & CInt(numLanguageID)  & " <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0," & DB_CG_ID & ") AND P_CustomerGroupID IN (0," & DB_CG_ID & ") AND CAT_CustomerGroupID IN (0," & DB_CG_ID & ") AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=1 ORDER BY P_ID DESC", numNewestClearanceList, true)

This is bugging me... .lol

[Andrew1986]

i found the problem but now i got a new one lol here is what i found

you had- ProductStats.Product_ID

then feild was

ProductStats.PS_ProductID

but now it shows 60 of the same product lol

[Rob]

ok.. the join is happening in the wrong place...

Can you post your updated line of code here?

[Andrew1986]

this is what i have now

numCount = 0
strQuery = LimitSQL("tblCactuShop" & TABLE_PREFIX & "ProductStats.PS_Date, tblCactuShop" & TABLE_PREFIX & "Products.P_ID, tblCactuShop" & TABLE_PREFIX & "Versions.V_Price, tblCactuShop" & TABLE_PREFIX & "Products.P_name" & numLanguageID & ", P_strapline" & CInt(numLanguageID) & " FROM ((((tblCactuShop" & TABLE_PREFIX & "Versions INNER JOIN tblCactuShop" & TABLE_PREFIX & "Products ON tblCactuShop" & TABLE_PREFIX & "Versions.V_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductStats ON tblCactuShop" & TABLE_PREFIX & "ProductStats.PS_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink ON tblCactuShop" & TABLE_PREFIX & "Products.P_ID= tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Categories ON tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_CategoryID = tblCactuShop" & TABLE_PREFIX & "Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "Suppliers ON tblCactuShop" & TABLE_PREFIX & "Products.P_SupplierID = tblCactuShop" & TABLE_PREFIX & "Suppliers.SUP_ID WHERE P_Name" & CInt(numLanguageID) & " <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0," & DB_CG_ID & ") AND P_CustomerGroupID IN (0," & DB_CG_ID & ") AND CAT_CustomerGroupID IN (0," & DB_CG_ID & ") AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=0 ORDER BY P_ID DESC", numNewestProductsList, true)

and the new error


Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/heinniem/includes/front_toplists.asp, line 50

line 50 is
numProductName3 = objRecordSet("P_Name" & CInt(numLanguageID) & ObjRecordSet.movenext)

[Andrew1986]

i do have msn handy if that would be easyer as then i could send you the .asp pages its self