Problem with a left join.......

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with a left join.......

Author

Topic

daveyboy
Starting Member

24 Posts

Posted - 2005-11-14 : 11:51:30

Simple peice of code which has had me scratching my head as to why it wont run.
It simply joining together a few tables for a report that gives figures regarding mortgage aplications on the previous day, a left join is used as the field 'secondaryApplicantId' may or may not exist.

error message in QA is
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'a' does not match with a table name or alias name used in the query.

SQL Server 2000 btw

select a.id, b.name, a.valueofproperty, a.mortgageamountrequired, c.annualincome as "primary income", e.description as "Primary Employment", d.annualincome as "secondary income", f.description as "Secondary Employment"

from [customerdata].[dbo].[mortgageenquiry] as a, [configuration].[dbo].[mortgagereasons] as b, [customerdata].[dbo].[applicantdetails] as c, [mconfiguration].[dbo].[employmentstatus] as e

left join [customerdata].[dbo].[applicantdetails] as d on a.secondaryApplicantId = d.id

left join [configuration].[dbo].[employmentstatus] as f on d.employmentstatusid = f.id

where a.mortgagereasonid = b.id

and a.primaryapplicantid = c.id

and c.employmentstatusid = e.id

and a.dateEnquiryMade between (CAST(STR(MONTH(getDate()))+'/'+STR(DAY(getDate() - 1))+'/'+STR(YEAR(getDate())) + ' 09:00:00' AS DateTime)) and getdate()

Totally has me stumped, any help would be great

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-14 : 12:01:12

try re-writing with proper INNER JOIN syntax to see if that helps.

daveyboy
Starting Member

24 Posts

Posted - 2005-11-14 : 12:10:35

Thanx, but unfortunately, i get the same error.

You can see what I’m trying to do with the left joins is retrieve the salary and employment status from applicantdetails and employmentstatus tables respectively, based on the secondaryapplicantid stored in the mortgageenquiry table.

And yet it doesn’t want to recognise that the mortgageenquiry table exists which is identified as a

The syntax is absolutely fine because the following runs ok


select distinct (a.userid), a.loanperiod, a.loanamountrequired, b.hitdate, b.referer, c.campaigntype, c.code, c.prodtype

from [customerdata].[dbo].[loanenquiry] a, [expertmatchtracking].[dbo].[archiveapplytracking] b

left join [expertmatchtracking].[dbo].[archivecampaignincoming] as c on b.campaignid = c.id

where b.hitdate between '01-nov-2005 00:00:00' and getdate()

and a.userid = b.userid

and a.ipaddress = b.ipaddress

very odd

wiltech
Yak Posting Veteran

51 Posts

Posted - 2005-11-15 : 07:06:12

If this doesn't work, please provide us with the DDL for the tables.

SELECT a.id, b.name, a.valueofproperty, a.mortgageamountrequired, c.annualincome as "primary income", 
	e.description as "Primary Employment", d.annualincome as "secondary income", 
	f.description as "Secondary Employment"
FROM [customerdata].[dbo].[mortgageenquiry] a
	INNER JOIN [configuration].[dbo].[mortgagereasons] b ON a.mortgagereasonid = b.id
	INNER JOIN [customerdata].[dbo].[applicantdetails] c ON a.primaryapplicantid = c.id
	INNER JOIN [mconfiguration].[dbo].[employmentstatus] e ON c.employmentstatusid = e.id
	LEFT JOIN [customerdata].[dbo].[applicantdetails] d on a.secondaryApplicantId = d.id
	LEFT JOIN [configuration].[dbo].[employmentstatus] f on d.employmentstatusid = f.id
WHERE a.dateEnquiryMade between DATEADD(Hour, -15, CAST(CONVERT(varchar(10), getdate(), 101) as datetime)) and getdate()

Thanks
Tony

daveyboy
Starting Member

24 Posts

Posted - 2005-11-16 : 07:36:20

Worked great,
Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources