Re: [KCFusion] sql help

[Adaryl \"Bob\" Wakefield]

That totally helped! Thanks after hours of staring at that I can move on as soon as the image of the code fades off my eyeballs. I only actually had to change one line Count(catQuestions.question_ID) as numQuestions I would never have gotten that in a million years.   Adaryl "Did you reboot?" Wakefield Aviator by passion Programmer by sheer force of will ----- Original Message ----- From: Jones Matt To: [EMAIL PROTECTED] Sent: Thursday, May 27, 2004 11:32 AM Subject: RE: [KCFusion] sql help Bob,   This should work on oracle, and should work for you on that other vendors system... my interpretation is this should give you all of the categories, whether they have questions or not:   SELECT Categories.Cat_num,     Categories.Category,     Count(catQuestions.question_ID) as numQuestions,     Questions.question,     (Select Count(Distinct C1.Cat_num) FROM Categories C1 Where C1.Category LIKE <cfqueryparam value="#form.letter#%" cfsqltype="CF_SQL_CHAR">) as numCats from  Categories left outer join catQuestions      on Categories.Cat_num = catQuestions.Cat_num       left outer join stores     on catQuestions.question_ID = Questions.question_ID and Questions.checked != 0 where   Categories.Category LIKE <cfqueryparam value="#form.letter#%" cfsqltype="CF_SQL_CHAR"> group by    Categories.Cat_Num, Categories.Category, Questions.question   order by 2      I may inadvertently have some oracle specific pieces in here, but I tried not to.   hth   Matt -----Original Message----- From: Adaryl "Bob" Wakefield [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 10:43 AM To: [EMAIL PROTECTED] Subject: [KCFusion] sql help Ive got this old SQL statement I wrote years ago Im trying to update it cause the database was not orginally normalized correctly. Now that it is the statement needs to change. Im only showing the whole thing for context. Im not going to bother to mask the table names i'd be too crazy.   SELECT Categories.Cat_num, Categories.Category, Count(Questions.question_ID) as numQuestions, 'question' = (Select TOP 1 Questions.Question FROM Questions, catQuestions WHERE Categories.Category LIKE <cfqueryparam value="#form.letter#%" cfsqltype="CF_SQL_CHAR"> AND Categories.Cat_num = catQuestions.Cat_num AND questions.question_ID = catQuestions.question_ID),   (Select Count(Distinct Cat_num) as catCount FROM Categories Where Categories.Category LIKE <cfqueryparam value="#form.letter#%" cfsqltype="CF_SQL_CHAR">) as numCats   FROM Categories, Questions, catQuestions WHERE Categories.Category LIKE <cfqueryparam value="#form.letter#%" cfsqltype="CF_SQL_CHAR"> AND Categories.Cat_num = catQuestions.cat_num AND catQuestions.question_ID = Questions.question_ID AND Questions.checked <> 0 Group BY Categories.Cat_Num, Categories.Category ORDER BY Categories.Category   My issue is with this line: AND Categories.Cat_num = catQuestions.cat_num AND catQuestions.question_ID = Questions.question_ID   It used to say categories.cat_num *= questions.cat_num but I had to bust up a gerund thus the above line.   The problem is I need all the categories reguardless if they have questions or not but when I try: AND Categories.Cat_num *= catQuestions.cat_num AND catQuestions.question_ID = Questions.question_ID   I get: [Macromedia][SQLServer JDBC Driver][SQLServer]The table 'catQuestions' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause   This: Categories.Cat_num *= catQuestions.cat_num AND catQuestions.question_ID =* Questions.question_ID Gets me funky results (the questions for each category are not tallied right) so at this point im out of ideas. Help?     Adaryl "Did you reboot?" Wakefield Aviator by passion Programmer by sheer force of will ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________