Re: EuSQL
- Posted by Alex Chamberlain <alex.chamberlain at tiscali.co.uk> Dec 07, 2005
- 578 views
Alex Chamberlain wrote: > > cklester wrote: > > > > Alex Chamberlain wrote: > > > > > > I have 2 tables - CATEGORIES & PRODUCTS. Each product belongs to a > > > Category > > > and the category's ID is stored in Products as CAT_ID. How do I select the > > > ID from > > > Category and count how many Products contain that ID as a CAT_ID? > > > > Here's a quick-and-dirty method: > > > > -- get the CAT_ID of the particular category you want based on its name > > -- for example, "Food" > > result = run_sql("SELECT CAT_ID FROM CATEGORIES WHERE CAT_NAME='Food'") > > -- test the value of result first to make sure Food was valid... > > result = sprintf("SELECT COUNT(*) FROM PRODUCTS WHERE > > CAT_ID=%d",{result[2][1][1]}) > > count = result[2][1][1] > > > > Or you could do it with a join (this isn't tested but gives the idea): > > > > SELECT COUNT(PRODUCTS.UNIQUE_ID) FROM CATEGORIES INNER JOIN PRODUCTS WHERE > > CAT_NAME='Food' > > I found the Solution: > > SELECT CATEGORIES.ID, CATEGORIES.NAME, CATEGORIES.DEFAULT_PRICE, > COUNT(PRODUCTS.CAT_ID) > FROM CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.ID = PRODUCTS.CAT_ID GROUP > BY CATEGORIES.ID, CATEGORIES.NAME, CATEGORIES.DEFAULT_PRICE; > > Not complecated of course - ha! > > Thanks, > Alex Actually, it only shows the categories that actually have a product in. Problem!?! Alex