[Resolved] Query syntax problem

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 2005 Forums

Transact-SQL (2005)

[Resolved] Query syntax problem

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-04 : 12:45:55

I have a query that is giving me a syntax error:

Server: Msg 1013, Level 15, State 1, Line 5
Tables or functions 'dbo.Event' and 'dbo.event' have the same exposed names. Use correlation names to distinguish them.

In this case I need BOTH of my inner joins and conditions to be met (on same table instance) before I can do my left join.

SELECT          batch_guid, event_guid, job_date, job_number, cost_code, qty, dbo.JobNoteEvent.Note, dbo.jobnoteevent.eventguid

FROM            #JobListTable
		inner join dbo.Event  on #JobListTable.batch_Guid = event.batchguid 
		inner join dbo.event on #Joblisttable.event_guid = event.relatedeventguid
		left join dbo.JobNoteevent on dbo.JobNoteEvent.EventGuid = event.eventguid
WHERE          job_number = 3505048 and cost_code = '05001' 
ORDER BY       job_date

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-04 : 13:04:07

You have two tables with the same name (dbo.Event). Use an alias to distinguish the tables and specifically qualify your field names with the alias.

dallr

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-04 : 13:04:12

quote:
Originally posted by snufse

I have a query that is giving me a syntax error:

Server: Msg 1013, Level 15, State 1, Line 5
Tables or functions 'dbo.Event' and 'dbo.event' have the same exposed names. Use correlation names to distinguish them.

In this case I need BOTH of my inner joins and conditions to be met (on same table instance) before I can do my left join.

SELECT batch_guid, event_guid, job_date, job_number, cost_code, qty, dbo.JobNoteEvent.Note, dbo.jobnoteevent.eventguid

FROM #JobListTable
inner join dbo.Event Ev on #JobListTable.batch_Guid = Ev.batchguid
inner join dbo.event Et on #Joblisttable.event_guid = Et.relatedeventguid
left join dbo.JobNoteevent on dbo.JobNoteEvent.EventGuid = Et.eventguid
WHERE job_number = 3505048 and cost_code = '05001'
ORDER BY job_date

If you are doing self join, you need alias.

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-04 : 13:10:25

On second look why are you using the same table (event) twice. From what I see only one table is needed.


SELECT batch_guid
     , event_guid
     , job_date
     , job_number
     , cost_code
     , qty
     , dbo.JobNoteEvent.Note
     , dbo.jobnoteevent.eventguid
FROM #JobListTable
     inner join dbo.Event  
       on #JobListTable.batch_Guid = event.batchguid and #Joblisttable.event_guid = event.relatedeventguid
     left join dbo.JobNoteevent 
       on dbo.JobNoteEvent.EventGuid = event.eventguid
WHERE job_number = 3505048 and cost_code = '05001' 
ORDER BY job_date

dallr

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-07 : 08:28:58

Perfect, this is what I was looking for. Thank you guys.

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-08 : 15:53:14

No problem glad to assist.

Dallr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources