You must be able to run it as a single call, or as a single TRANSACTION?
What do you mean by "single call"? One PHP command? One command on
the commandline? one script run?
-Sheeri
On 4/4/06, Ed Reed <[EMAIL PROTECTED]> wrote:
> 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.
>
> >>> Peter Brawley <[EMAIL PROTECTED]> 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
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
