Adding new Table to Query

This is a discussion on "Adding new Table to Query" within the Classic ASP section. This forum, and the thread "Adding new Table to Query are both part of the Program Your Website category.


 Subscribe in a reader

Go Back   Webforumz.com > Main Forums > Program Your Website > Classic ASP
Reload this Page Adding new Table to Query

Notices
Please Register!
Webforumz.com is an education based community supporting web designers, developers and those who are new and learning. If you are a designer, developer, learning web design or are thinking about a career in web design then you have come to the right place. Register for free now to access more information and to participate in discussion and debate.




Reply
Page 1 of 2 1 2 >
 
LinkBack Thread Tools
  #1  
Old Jul 31st, 2007, 11:02
Up'n'Coming Member
Join Date: Jul 2007
Location: Barry
Age: 22
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Post Adding new Table to Query
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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote

  #2  
Old Jul 31st, 2007, 11:14
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
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)
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #3  
Old Jul 31st, 2007, 11:15
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
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!
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #4  
Old Jul 31st, 2007, 11:32
Up'n'Coming Member
Join Date: Jul 2007
Location: Barry
Age: 22
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Re: Adding new Table to Query
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 :
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #5  
Old Jul 31st, 2007, 11:35
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
lol.... you need a join.... one sec
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #6  
Old Jul 31st, 2007, 11:37
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
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?
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #7  
Old Jul 31st, 2007, 11:48
Up'n'Coming Member
Join Date: Jul 2007
Location: Barry
Age: 22
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Re: Adding new Table to Query
Feilds = PD_ID , PS_ProductID, PS_Date, PS_IP

i need the data from PS_Date
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #8  
Old Jul 31st, 2007, 12:03
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
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)
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Last edited by Rob; Jul 31st, 2007 at 12:05.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #9  
Old Jul 31st, 2007, 12:06
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
Oops... just spotted a bug... refresh the above post
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #10  
Old Jul 31st, 2007, 12:07
Up'n'Coming Member
Join Date: Jul 2007
Location: Barry
Age: 22
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Re: Adding new Table to Query
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #11  
Old Jul 31st, 2007, 12:09
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
You may want to check that table name... like I said, this is all from memory
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #12  
Old Jul 31st, 2007, 12:12
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
Spotted it... I used CactusShop instead of CactuShop

Please amend and retry
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #13  
Old Jul 31st, 2007, 12:21
Up'n'Coming Member
Join Date: Jul 2007
Location: Barry
Age: 22
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Re: Adding new Table to Query
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 :
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #14  
Old Jul 31st, 2007, 12:31
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
This is doing my head in.... give me a while to troubleshoot this..
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #15  
Old Jul 31st, 2007, 12:33
Up'n'Coming Member
Join Date: Jul 2007
Location: Barry
Age: 22
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Re: Adding new Table to Query
take all the tme you need as you are being greatful enough to help
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #16  
Old Jul 31st, 2007, 12:39
Rob's Avatar
Rob Rob is online now
Webforumz Founder
Join Date: Jul 2003
Location: Southern UK
Age: 34
Posts: 3,194
Blog Entries: 7
Thanks: 27
Thanked 24 Times in 21 Posts
Re: Adding new Table to Query
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
__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #17  
Old Jul 31st, 2007, 12:40
Up'n'Coming Member
Join Date: Jul 2007
Location: Barry
Age: 22
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Re: Adding new Table to Query
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!