OpenEuphoria

Re: EuSQL

• Posted by cklester <cklester at yahoo.com> Dec 07, 2005
• 541 views

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'

-=ck
"Programming in a state of Euphoria."
http://www.cklester.com/euphoria/