List:General Discussion« Previous MessageNext Message »
From:Ed Reed Date:April 4 2006 8:34pm
Subject:very long query for such a simple result
View as plain text  
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')),')')),'');



Thread
very long query for such a simple resultEd Reed4 Apr
  • Re: very long query for such a simple resultSGreen4 Apr
  • Re: very long query for such a simple resultPeter Brawley4 Apr
Re: very long query for such a simple resultEd Reed4 Apr
Re: very long query for such a simple resultEd Reed4 Apr
  • Re: very long query for such a simple resultPeter Brawley5 Apr
  • Re: very long query for such a simple resultsheeri kritzer5 Apr
Re: very long query for such a simple resultEd Reed5 Apr
  • Re: very long query for such a simple resultPeter Brawley5 Apr
Re: very long query for such a simple resultEd Reed6 Apr
  • Re: very long query for such a simple resultBarry6 Apr
  • Re: very long query for such a simple resultPeter Brawley6 Apr