asked on

nested alias' in SQL join

I have a select statement which includes a join as below:-

...
left join dubsqlv.cms.dbo.CommitmentAudit cmtaudit
on (cmt.ID = cmtaudit.CommitmentID
and cmtaudit.datecreated = (
                              select
                              max(cmtaudit2.datecreated)
                              from dubsqlv.cms.dbo.CommitmentAudit cmtaudit2
                              where cmtaudit.commitmentid = cmtaudit2.commitmentid
                        )
)

...

I am trying to return a table of items called "commitments". Specifically I want to include the commitmeent ID and the most recent "datecreated" for a given commitment from it's audit table.

When I try running the query I get the error...
"The column prefix 'cmtaudit' does not match with a table name or alias name used in the query."

If I comment out the and condition the query works. The bit it doesn't seem to like is the cmtaudit in the nested statement. How can I pass the cmtaudit record to the nested query. Is there something wrong with my syntax?

ASKER CERTIFIED SOLUTION

wstuph

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

DireOrbAnt

Don't put everything in the WHERE:
left join dubsqlv.cms.dbo.CommitmentAudit cmtaudit
on cmt.ID = cmtaudit.CommitmentID
WHERE cmtaudit.datecreated = (
select
max(cmtaudit2.datecreated)
from dubsqlv.cms.dbo.CommitmentAudit cmtaudit2
where cmtaudit.commitmentid = cmtaudit2.commitmentid
)

molar

ASKER

Hi DireOrbAnt
I guess that's what wstuph meant (it's what how I understood it anyway). I'm just giving it a go now.

molar

ASKER

That did it.

Thanks to you both.