Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that
I have to make work together and the problemreport table does not reference the
employeeid in the employees table. It was all create about ten years ago and
the data has just always been migrated to the db du jour. I'm currently using
MySQL 4.1x but most importantly I must be able to run the entire query in a
single call.
Thanks for the help.
>>> Peter Brawley <[EMAIL PROTECTED]> 4/4/06 2:35:49 PM >>>
Ed,
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: ProblemReportsFields: PRNo, Status, Priority, Responsible
Table: EmployeesFields: 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
