List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 4 2006 8:56pm
Subject:Re: very long query for such a simple result
View as plain text  
"Ed Reed" <EReed@stripped> wrote on 04/04/2006 04:34:29 PM:

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

You are new at this, aren't you?  First thing you need to do is to let us 
see exactly what we need to deal with. Please respond with  the results of 
the following two commands

SHOW CREATE TABLE Employees\G
SHOW CREATE TABLE ProblemReports\G

We (the list members) will be able to help straighten you out from there 
(there are lots of things we need to talk about but I think that we should 
get you working first, ok?)

Always CC the list (or hit the REPLY TO ALL button or whatever you have in 
your email client). That way everyone on the list stays informed of the 
progress of this issue.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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