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

rcowen00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dirknibleckCommented:
The problem is in this part of the query:

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

You are selecting *, and per your ON statement, loanKey exists in tables loan and audit, so it's being included in the columns twice. You need to specify the specific columns you need.

I might suggest that you change it to this:

SELECT lb.*, columns you need from ab, histtblNrIssues.*
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.