Re: Another SQL Question

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 .........................................