Query problem

[Guest]

Hi,

I've the following query:
SELECT agentsq.Agent, Count(tasks.DR) AS CountOfDR, Count(tasks.MA) AS
CountOfMA, Count(tasks.SHM) AS CountOfSHM, Count(tasks.NA) AS CountOfNA,
Count(tasks.SH) AS CountOfSH
FROM agentsq LEFT JOIN tasks ON (agentsq.Agent = tasks.NA) AND
(agentsq.Agent = tasks.SHM) AND (agentsq.Agent = tasks.MA) AND (agentsq.Agent
= tasks.DR) AND (agentsq.Agent = tasks.SH)
GROUP BY agentsq.Agent;
But actually the COUNT function does not count the rows where (for
example) agent X did the DR task 3 times...
cAN ANYBODY HELP?

 

[Smartin]

Hi Pietro,

I am guessing your tasks table is not normalized. Your query demands
agentsq.Agent == tasks.Agent on every row of tasks across four
columns. This suggests DR, MA, SHM and NA are in fact independent
measures of Agent?

If I am right, you should decompose tasks into four tables, one each
for DR, MA, SHM and SH measures. This being the case then you can join
agentsq to each of the four tables and get correct counts.