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