OpenEuphoria

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