Link to home
Create AccountLog in
Avatar of rcowen00
rcowen00Flag for United States of America

asked on

MySql Nested Query - Alias Problem

I am trying to have one query that will give me a count and average of 2 different time period that the user enters.  I have query that I get #1060 - Duplicate column name 'loanKey' error on.  I think it is a problem with alias, or lack of, but I am missing it.   If I remove lines 10 through 27 it runs as expected.  Any suggestions?  Thank you


Name  Current Ct   Historical  Ct   Current Avg   Historical Avg
Tom         5                   10                     1.5                  3.0
SELECT CONCAT( uwLastName, uwFirstName ) AS  'UW Name', 
AVG( NrIssues ) AS  'avgIssues', 
COUNT( a.auditID ) AS  'nrAudits', 
AVG( histNrIssues ) AS  'histavgIssues', 
COUNT( ab.auditID ) AS  'histnrAudits'
FROM loan l
INNER JOIN audit a ON a.loanKey = l.loanKey
INNER JOIN (

SELECT * 
FROM loan lb
INNER JOIN audit ab ON ab.loanKey = lb.loanKey
INNER JOIN (

SELECT auditID, COUNT( * ) AS histNrIssues
FROM loanFinding lfb
GROUP BY (
auditID
)
)histtblNrIssues ON histtblNrIssues.auditID = ab.auditID
WHERE branchKey =  '66'
AND lb.active =  '1'
AND ab.auditTypeKey =  '1'
AND ab.trackingDate
BETWEEN  '2010-09-01'
AND  '2010-09-15'
) AS hist


INNER JOIN (

SELECT auditID, COUNT( * ) AS NrIssues
FROM loanFinding lf
GROUP BY (
auditID
)
)tblNrIssues ON tblNrIssues.auditID = a.auditID
WHERE branchKey =  '66'
AND l.active =  '1'
AND a.auditTypeKey =  '1'
AND a.trackingDate
BETWEEN  '2010-09-01'
AND  '2010-09-30'
GROUP BY uwLastName
LIMIT 0 , 30

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of dirknibleck
dirknibleck

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account