Thx Alnisa,
I had tried your suggested approach earlier.
I just tried it again I got the following from SQL*Plus:
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
JServer Release 8.1.6.3.0 - Production
SQL> select cat.id, cat.name, count(faq_cat.cat_id) as num_id
2 from mdfaq_faq_cat faq_cat, mdfaq_category cat
3 where faq_cat.cat_id = cat.id group by cat.name;
select cat.id, cat.name, count(faq_cat.cat_id) as num_id
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
After removing the Group By:
SQL> select cat.id, cat.name, count(faq_cat.cat_id) as num_id
2 from mdfaq_faq_cat faq_cat, mdfaq_category cat
3 where faq_cat.cat_id = cat.id;
select cat.id, cat.name, count(faq_cat.cat_id) as num_id
*
ERROR at line 1:
ORA-00937: not a single-group group function
After removing the cat.* queries:
SQL> select count(faq_cat.cat_id) as num_id
2 from mdfaq_faq_cat faq_cat, mdfaq_category cat
3 where faq_cat.cat_id = cat.id;
NUM_ID
----------
1
Replacing count() with the cat.* queries:
SQL> select cat.id, cat.name
2 from mdfaq_faq_cat faq_cat, mdfaq_category cat
3 where faq_cat.cat_id = cat.id;
ID NAME
---------- ----------------------------------------------------------------
4 MicroStation
Simple select from category:
SQL> select * from mdfaq_category;
ID NAME
---------- ----------------------------------------------------------------
1 Applications
2 Solid Edge
4 MicroStation
It seems like this is a common type of query:
Select a, b, count(c.e) as d where c.e = a.e;
Any other suggestions?
Thx in advance,
Barry
"Alnisa Allgood" <[EMAIL PROTECTED]> wrote in message
news:p05100300b79a3aa685c8@[63.202.15.82]...
> At 4:07 PM -0700 8/10/01, Barry Prentiss wrote:
> >Hi,
> > I am writing a FAQ machine in PHP using Oracle 8.1.6.
> > I can't figure out what's not working in my SQL query.
> > I've spent two days on the Oracle site, to no avail.
> > I have three tables:
> > FAQ[ID,QUESTION,ANSWER]
> > FAQ_CAT[FAQ_ID,CAT_ID]
> > CAT[ID,NAME] (category)
> >
> > I'm trying to list CAT.ID, CAT.NAME and count(*) where count(*) is the
> >count of all FAQs in each category.
> > My latest attempt looks something like this:
> > select c.id, c.name, a.num from cat c,(select count(*) num from faq_cat
f
> >where f.cat_id = c.id) a;
> > I keep getting an 'invalid column name' at the last 'c.id'...
> >
>
> Well first off, I should mentioned that I'm not familiar with the
> specifics of how Oracle implements SQL, that said...
>
> I noticed a few items,One: you don't define the returned Count(*),
> which means it can't be used by PHP. Second: you've created a
> subquery where one isn't needed.
>
> I use php and mySQL, but if I were creating the query, I'd basically
> want the statement to read like so:
>
> Select category id, category name, and the count of the
number
> or category articles from the tables category and FAQ
Category.
> Limited the results to where category in FAQ equal Category
ID
> in Category. Display by category name.
>
> I would phrase it something like so:
>
> SELECT cat.id, cat.name, COUNT(faq_cat.cat_id) AS num_id FROM
> faq_cat, cat WHERE faq_cat.id = cat.id GROUP BY cat.name
>
> I'm not certain if this will work exactly as is in Oracle, but it
> should get you closer.
>
> Alnisa
> --
> .........................................
> Alnisa Allgood
> Executive Director
> Nonprofit Tech
> (ph) 415.337.7412 (fx) 415.337.7927
> (url) http://www.nonprofit-techworld.org
> (url) http://www.nonprofit-tech.org
> (url) http://www.tech-library.org
> .........................................
> Nonprofit Tech E-Update
> mailto:[EMAIL PROTECTED]
> .........................................
> applying technology to transform
> .........................................
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
