Afternoon everyone,
Sorry, don't you hate the fact that the tab key submits your emails on
web clients!?
Anyway please ignore that last partial email, I've got myself stuck with
some SQL. What it boils down to is...
SELECT
wordgroup.Title AS `Keyword Group`,
site.Site_name AS Site,
questiontext.SanitisedQuestion AS Question,
site.SiteID AS sid,
question.QuestionID AS qid,
COUNT(wordgroupanalysis.AnswerFK) AS Hits
FROM
wordgroup
LEFT JOIN wordgroupanalysis ON wordgroupanalysis.WordGroupFK =
wordgroup.WordGroupID
LEFT JOIN answer ON answer.AnswerID = wordgroupanalysis.AnswerFK
LEFT JOIN answertext ON answertext.AnswerTextID =
answer.AnswerTextFK
LEFT JOIN question ON question.QuestionID = answer.QuestionFK
LEFT JOIN questiontext ON questiontext.QuestionTextID =
question.QuestionTextFK
LEFT JOIN individual ON individual.IndividualID =
answer.PeopleFK
LEFT JOIN site ON individual.SiteFK = site.SiteID
WHERE
Answer <> 'NULL' AND
site.Site_name <> 'HQ' AND
individual.Date_consumed BETWEEN "2006-01-01" AND "2007-05-01"
GROUP BY
wordgroup.Title,
site.Site_name,
questiontext.SanitisedQuestion
ORDER BY NULL;
...returns me the number of "hits" (matches) of each keyword group against
the text based responses to various questions in a Questionnaire that is
sent out to different client sites, something like...
+---------------+------------------+--------------------------------------------------------------------------------------------+------+--------+------+
| Keyword Group | Site |
Question
| sid | qid | Hits |
+---------------+------------------+--------------------------------------------------------------------------------------------+------+--------+------+
| Drinks | SHERWOOD PINES | if you were less than satisfied with
any of the above areas please provide further details | 378 | 356 | 39
|
| Drinks | BRACKNELL | if you were less than satisfied with
any of the above areas please provide further details | 382 | 431 | 75
|
| Drinks | DEAN | if you were less than satisfied with
any of the above areas please provide further details | 379 | 356 | 44
|
All well and fine but then I want to get the total number of answers to
that question whether or not they matched against the keyword group so that
I can make the "hits" into a percentage, I can get the total answers on an
individual basis like so...
SELECT COUNT(*) FROM answer LEFT JOIN individual ON individual.IndividualID=
answer.PeopleFK WHERE answer.QuestionFK = 356 AND individual.SiteFK = 378
AND individual.Date_consumed BETWEEN "2006-01-01" AND "2007-05-01";
...but it has to be done all in one query (crapy Crystal Reports, don't
ask). I've tried it as a subquery and as a function but it states that
"Select 2 was reduced during optimization" when done as a function and seems
to be trying to join every count against every row in the main table when
run as a subquery and then grinds to a halt, I need it to run the subquery
as an outer join I think onto the answer table but I can't get my head round
it?
Anyone know how I can do this? I'll personally have your babies!
--
Regards,
Phil
