Forum Moderators: mack
For example:
1. Jeweleries
-- 1.1 Gold
----- 1.1.1 Gold Product 1
----- 1.1.2 Gold Product 2
----- 1.1.3 Gold Product 3
----- 1.1.4 Gold Product 4
1.2 Silver
----- 1.2.1 . Earrings
------- 1.2.1.1 Silver Earring Product 1
------- 1.2.1.2 Silver Earring Product 2
------- 1.2.1.3 Silver Earring Product 3
----- 1.2.2 . Pendants
------- 1.2.2.1 Silver Pendant Product 1
------- 1.2.2.2 Silver Pendant Product 2
....
As you can see products are in different level of categories
, how can I count count it using statement assuming that I have the following tables:
CATEGORIES TABLE
--------------------------------------------------
categ_id
categ_name
parent_id
PRODUCTS TABLE
--------------------------------------------------
prod_id
prod_name
categ_id
Please advise.
For instance, how would you get a count of Silver Jewelery?
select count(*) from products where categ_id in (select categ_id from categories where paren_id = 12)
This would get you a count of the products within categories that have category 12 as their parent.
There's a great book out by Michael Hernandez called "SQL Queries for Mere Mortals" its very well written and can give you some of the SQL statements that you're looking for. I'm halfway through it myself and it has helped me a great deal with my work as a webmaster managing a database-driven site. Its platform independant book that will work for Access, SQL Server, and I believe Oracle and a few others.
Check it out on amazon.
good luck.
Patrick
select count(*) from products where categ_id in (select categ_id from categories where paren_id = 12)
From the dataset itnecor specified, this query would only pull back 2 subsubcategories (1.2.1 and 1.2.2). I assume (s)he would want the actual products under the subsubcategories.
To get the #products under a subsubcategory (e.g. 1.2.1) you could use something like:
select count(prods.prod_id) from CATEGORIES cat, CATEGORIES subcat, CATEGORIES subsubcat, products prods where cat.categ_id = $whatever and subcat.parent_id = cat.categ_id and subcat.categ_id = subsubcat.parent_id and subsubcat.categ_id = prods.categ_idFor a subcategory (e.g. 2.1), you would remove the 3rd reference to the CATEGORIES table.
arran.
