Re: Another SQL Question

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" <allgood2@slip.net> 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:nonprofit-tech-subscribe@egroups.com > ......................................... > applying technology to transform > .........................................