Your Cart

Shopping Cart Items: 0   Sub-Total : $0.00    View Cart    Checkout
Welcome, Guest ( Customer Panel | Login )


Home | Profile | Register | Active Topics | Members | Search | FAQ


Forgot your Password?
 All Forums
 VPCart Forum
 General help me questions
 Product category and subcategorries query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hito_kun
Starting Member

Mexico
3 Posts
Posted - July 02 2009 :  16:57:08  Show Profile  Reply with Quote
Hello there.

I want to create a table that includes the ccode, the cname, categories and subcategories of my store.

Im a n00b in SQL, and the only thing I can't get is the subcategories.

This is what my query looks like, my database runs in a MS SQL Server 2000, if that helps.

SELECT dbo_products.ccode, dbo_categories.catdescription
FROM dbo_categories INNER JOIN dbo_products ON dbo_categories.categoryid = dbo_products.ccategory;


Well, I hope someone can helpe with this thing, I've been all morning trying but Im lost.

carfin
VP-CART Expert

United Kingdom
948 Posts
Posted - July 03 2009 :  03:59:18  Show Profile  Reply with Quote
Hi,

you will need a SQL statement like:

SELECT dbo_products.ccode, dbo_products.cname, dbo_prodcategories.intcategoryid, dbo_categories.catdescription, dbo_categories.highercategoryid
FROM dbo_products INNER JOIN (dbo_categories INNER JOIN dbo_prodcategories ON dbo_categories.categoryid = dbo_prodcategories.intcategoryid) ON dbo_products.catalogid = dbo_prodcategories.intcatalogid
ORDER BY dbo_products.ccode;

This should give you a list of all products, and the id and description of the category that the products are assigned to and the id of any higher categories.

Regards,
Carrol
www.deanston-electrical.co.uk
Go to Top of Page

Hito_kun
Starting Member

Mexico
3 Posts
Posted - July 03 2009 :  11:08:10  Show Profile  Reply with Quote
Thanks for the answer.

Im getting closer to what I need, but instead of this output:

ccode1	cname1	intcategoryid1	catdescription1	highercategoryid1
ccode1	cname1	intcategoryid2	catdescription2	highercategoryid2
ccode1	cname1	intcategoryid3	catdescription3	highercategoryid3
ccode1	cname1	intcategoryid4	catdescription4	highercategoryid4


Is there a wayo to get something like this:


ccode1	cname1	catdescription1	(sub)catdescription1_1	(sub)catdescription1_2	(sub)catdescription1_3
ccode1	cname1	catdescription2	(sub)catdescription2_1	(sub)catdescription2_2	(sub)catdescription2_3
ccode2	cname2	catdescription1	(sub)catdescription1_1	(sub)catdescription1_2	(sub)catdescription1_3


basically having in the same row the product code and name + main category of the product and its subcategories (The store has same items in different categories sometimes).
Go to Top of Page

carfin
VP-CART Expert

United Kingdom
948 Posts
Posted - July 03 2009 :  11:57:07  Show Profile  Reply with Quote
The SQL statement I gave you does list the subcategories that the products are in, as well as the main category. If the value of the field highercategoryid is 0 it means that the product is in a main category, any other number, then it is a subcategory (unless of course you have subcategories of subcategories) I cannot think of any other way to get the information you are requesting from a single SQL statement. Perhaps someone else may know of another way.

Regards,
Carrol
www.deanston-electrical.co.uk
Go to Top of Page

Hito_kun
Starting Member

Mexico
3 Posts
Posted - July 03 2009 :  12:35:42  Show Profile  Reply with Quote
Well, thanks for the help.

And if with a query is not possible, I'll use any suggestion.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000
0 Item(s)
$0.00
  • Your Cart Is Empty