Ed,
>Can someone help me simplify this query please? It's meant
>to return a single string result that looks something like this,
>"You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)"
The big slowdown in your query is likely the join on
ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName))
Eek. Does the employees table not have a primary key, and does the
problemreports table not reference that key? If not, I would think a
first priority would be to fix that.
Meanwhile, you can simplify your monster query by writing the user's
problem priorities to a temp table, then doing a simple pivot on
priority to generate your sentence. Something like ...
DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
SELECT COUNT(*) INTO @n FROM ProbSum;
SELECT
IF( @n = 0,
'',
CONCAT( 'You have',
@n,
'Problem Report',
IF(@n=1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM probsum;
DROP TEMPORARY TABLE probsum;
All this would be easier in a stored procedure, if you have MySql 5.
PB
-----
Ed Reed wrote:
Can someone help me simplify this query please? It's meant to return a single string result that looks something like this,
"You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)"
The relavent columns from the two tables are
Table: ProblemReports
Fields: PRNo, Status, Priority, Responsible
Table: Employees
Fields: Firstname, Lastname, DateTerminated, UserName
Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))>0,
If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ',
(Select Priority From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),
Concat('You have ', @a, ' open Problem Reports: Priorities(',
(Select ConCat('High=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='High')),', ',
(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Med')),', ',
(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Low')),')')),'');
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006
|
