OpenEuphoria

Re: EuSQL

• Posted by Alex Chamberlain <alex.chamberlain at tiscali.co.uk> Dec 07, 2005
• 565 views

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