Since I'm learning about Left joins and am trying to figure out a way
to quickly gather info from Database all at once, here is the SQL
statement. Please give me feedback how I should improve it for better
performance. There are around 10 users that will hit database like
this along with other left joins ever minute. Thanks in advance:
SELECT
QA.ID,
Brands.Brand,
QA.KeyDate,
A2.LastName + ', ' + A2.FirstName as Reviewer,
A1.LastName + ', ' + A1.FirstName as Operator,
Batch.[order],
Errortypes.[Description],
ErrorTypes.points,
Batch.Comments,
(SELECT
sum(Errortypes.points)
FROM
ErrorTypes,QAErrors,Batch,QA
WHERE
Errortypes.Id=QAErrors.ErrorTypeID
AND
QAErrors.ID=Batch.QEID
AND
Batch.QAID=QA.ID
AND
QA.Batch='464005807') as points,
(SELECT
count(*)*100
FROM
Batch,QA
WHERE
Batch.QAID=QA.ID
AND
QA.Batch='464005807') as total
FROM
Brands,Associates A1, Associates A2,QA
Left Join
Batch
ON
Batch.QAID=QA.ID
Left Join
QAErrors
ON
QAErrors.ID=Batch.QEID
Left Join
Errortypes
On
ErrorTypes.id=QAErrors.ErrorTypeID
WHERE
QA.Batch='464005807'
AND
A1.ID=QA.OperatorID
AND
A2.ID=QA.QAID
AND
Brands.ID=QA.BrandID;
RESULT (delimited by ,):
100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
Teresa,244770323,Error in Name,8.0,last name mispelled,24.0,600
100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
Teresa,244770323,Error in Address,8.0,blvd mispelled,24.0,600
100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
Teresa,244770324,Error in Address,8.0,BLVD MISSPELLED,24.0,600
100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
Teresa,244770325,NULL,NULL,NULL,24.0,600
100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
Teresa,244770322,NULL,NULL,NULL,24.0,600
100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
Teresa,244770321,NULL,NULL,NULL,24.0,600
--
Power to people, Linux is here.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
