1. EuSQL
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?
Thanks,
Alex
2. Re: EuSQL
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/
3. Re: EuSQL
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?
<snip>
> 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'
>
Or, to get a list of all categories:
SELECT CATEGORIES.ID, COUNT(PRODUCTS.ID)
FROM CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.ID = PRODUCTS.CAT_ID
GROUP BY CATEGORIES.ID
Matt Lewis
4. Re: EuSQL
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
5. Re: EuSQL
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
6. Re: EuSQL
Alex Chamberlain wrote:
>
> Alex Chamberlain wrote:
> > 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;
> >
>
> Actually, it only shows the categories that actually have a product in.
> Problem!?!
This is what outer (a LEFT JOIN, in this case) joins are for:
SELECT CATEGORIES.ID, CATEGORIES.NAME, CATEGORIES.DEFAULT_PRICE,
COUNT(PRODUCTS.CAT_ID)
FROM CATEGORIES LEFT JOIN PRODUCTS ON CATEGORIES.ID = PRODUCTS.CAT_ID
GROUP BY CATEGORIES.ID, CATEGORIES.NAME, CATEGORIES.DEFAULT_PRICE;
Matt Lewis
7. Re: EuSQL
- Posted by cklester <cklester at yahoo.com>
Dec 07, 2005
-
Last edited Dec 08, 2005
Matt Lewis wrote:
>
> This is what outer (a LEFT JOIN, in this case) joins are for:
>
> SELECT CATEGORIES.ID, CATEGORIES.NAME, CATEGORIES.DEFAULT_PRICE,
> COUNT(PRODUCTS.CAT_ID)
> FROM CATEGORIES LEFT JOIN PRODUCTS ON CATEGORIES.ID = PRODUCTS.CAT_ID
> GROUP BY CATEGORIES.ID, CATEGORIES.NAME, CATEGORIES.DEFAULT_PRICE;
I've used tons of INNER JOINs on my <a
href="http://www.myprayerlist.org/">MyPrayerList</a> site.
I don't know if any of those need to be LEFT JOINs, RIGHT JOINs, OUTER JOINs.
Is there a good, CLEAR, and comprehensive resource on the 'net for
understanding the JOINs?
-=ck
"Programming in a state of Euphoria."
http://www.cklester.com/euphoria/
8. Re: EuSQL
I thought EuSQL's new documentation was good when I read it!
9. Re: EuSQL
I'm not sure if this is a problem with EuSQL?
I have a program where people can add categories. However, they do not show up
when i make a query because I query like this: "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;" using an INNER join. If I use a LEFT
join, the categories with no products appear to have one. Problem!?!
Thanks,
Alex
10. Re: EuSQL
Alex Chamberlain wrote:
>
> I'm not sure if this is a problem with EuSQL?
>
> I have a program where people can add categories. However, they do not show
> up when i make a query because I query like this: "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;" using an INNER join. If I use a LEFT join, the
> categories
> with no products appear to have one. Problem!?!
Sorry I failed to mention that an INNER join does not show categories with no
product!
Thanks,
Alex
11. Re: EuSQL
Alex Chamberlain wrote:
>
> Alex Chamberlain wrote:
> >
> > I'm not sure if this is a problem with EuSQL?
> >
> > I have a program where people can add categories. However, they do not show
> > up when i make a query because I query like this: "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;" using an INNER join. If I use a LEFT join, the
> > categories
> > with no products appear to have one. Problem!?!
>
> Sorry I failed to mention that an INNER join does not show categories with
> no product!
Yes, that's basically the definition of an inner join. You need to use
a left or right join to allow for null results from another table.
Matt Lewis
12. EuSQL
I'm still getting the pretty_source()
not defined error
sprint.e and print.e are included in the directory.
Ken Rhodes
---------------------------------------------
Jonas Temple wrote:
Matt,
Also tried EuSql today and I get an error that
pretty_source() is
not defined.
Oops. I added sprint.e, but never put in the include
file. I think I
did this while developing EDB, and EuSQL was just
using EDB's include
statement. I've fixed this and re-uploaded.
Matt
13. EuSQL
I've sent Rob EuSQL, which is my SQL implementation for EDS. It's still
pretty raw, but it works, as long as you're careful. For those of you who
have been working on something like this, please let me know what you think.
I'd also be interested to hear from people who are using EDS currently, how
they're getting data out of their databases.
You can also get EuSQL (and my latest version of win32lib--I'm not sure what
the difference is with the sourceforge version, since I haven't had a chance
to look at it) at:
Matt Lewis
14. EuSQL
I just sent Rob my latest update of EuSQL. It's also on my web page now.
I've added support for several column functions: SUM, COUNT and AVG, as well
as GROUP BY (used with the column functions) and SELECT DISTINCT.
I'm also wondering if anyone else has made any progress on their db projects
(Mike Nelson, Buddy Hylberg ?)
In any case, my current plans are to implement calculated fields and
indices. Then I'll start working on CREATE, UPDATE, INSERT, etc type
queries.
Matt Lewis
http://www.realftp.com/matthewlewis
15. Re: EuSQL
Matt,
My project is coming along very slowly because of lack of time--I work full
time and have a 10-month old and a 4 1/2 months pregnant wife--I hardly ever
get to write code anymore, but I'm working on it when I can. I have an
indexing scheme that allows binary search for queries on any field, not
just the key. I also have code that maitains referntial integrity between
linked tables. I'm struggling with the best way to implemnt joins.
-- Mike Nelson
----- Original Message -----
From: "Matthew Lewis" <MatthewL at KAPCOUSA.COM>
To: <EUPHORIA at LISTSERV.MUOHIO.EDU>
Sent: Friday, November 03, 2000 11:03 AM
Subject: EuSQL
> I just sent Rob my latest update of EuSQL. It's also on my web page now.
> I've added support for several column functions: SUM, COUNT and AVG, as
well
> as GROUP BY (used with the column functions) and SELECT DISTINCT.
>
> I'm also wondering if anyone else has made any progress on their db
projects
> (Mike Nelson, Buddy Hylberg ?)
>
> In any case, my current plans are to implement calculated fields and
> indices. Then I'll start working on CREATE, UPDATE, INSERT, etc type
> queries.
>
> Matt Lewis
> http://www.realftp.com/matthewlewis
16. Re: EuSQL
- Posted by budmeister1 at JUNO.COM
Nov 04, 2000
Matthew,
I've been following your EuSQL project with great interest, but alas I
haven't
had much time to delve back into the world of Euphoria... I'm a college
freshman,
and spare time is about as foreign as clean dorm rooms ;)
However, a friend and I have managed to get a Linux box setup and I'm
currently
working on getting it ready to move my website to, along with some other
goodies.
(For those that are interested, it's located at littlebro.homeip.net, and
it's
running TurboLinux Server 6.0).
As far as the EDS Toolbox goes, I'm still maintaining it, but haven't
really done
much to improve it beyond bug fixes. I'm not sure how popular it is, but
if webserver
logs are any indication, it seems to be in demand. Anyone have their 2
cents to toss
in?
Btw, if you're wondering what language I *have* been coding in, it starts
with a C
and ends with a C... not to mention that the development server we use is
an old
VAX with OpenVMS on it ;) If you think that's bad, look at the TrueBASIC
class that
I managed to skip out of!
----->Buddy
budmeister1 at juno.com
http://tenbux.iwarp.com/
