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')),')')),'');