List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 5 2006 5:13am
Subject:Re: very long query for such a simple result
View as plain text  
Ed,e:
> 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.
>   
OK, that's doable in a subquery, and you can get the total from SUM(1), 
so something like ...

SELECT
  IF( SUM(1) = 0,
      '',
      CONCAT( 'You have ',
              SUM(1),
              ' Problem Report',
              IF(SUM(1) = 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 (
  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'
) AS priorities;

PB

-----
>   
>>>> Peter Brawley <peter.brawley@stripped> 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  
>
>   
> ------------------------------------------------------------------------
>
> 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
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006
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