Re: Another SQL Question

Thx Again Alnisa, You were very close, and instrumental in solving this seemingly simple query problem. The actual query that worked looks like this: SQL> select cat.id, cat.name, count(faq_cat.cat_id) as num 2 from mdfaq_faq_cat faq_cat, mdfaq_category cat 3 where faq_cat.cat_id (+) = cat.id 4 group by cat.id, cat.name; ID NAME ---------- ---------------------------------------------------------------- NUM ---------- 1 Applications 0 2 Solid Edge 0 4 MicroStation 1 Thx for your help, 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 > .........................................