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