[Resolved] Use correlation names

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] Use correlation names

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-18 : 12:27:05

I have a working sp:

SELECT           dbo.Job.CompanyJobId, dbo.Item.UnitOfMeasure, dbo.Job.Name, dbo.Job.ChangeDate, 
                 dbo.Region.CompanyRegionID, dbo.Job.Active,
                 sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
                 sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production

FROM             dbo.job 
                 inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
		 inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
                 left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                 left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
                 inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                 inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                 left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
			
WHERE            dbo.Job.CompanyJobId = 3505048 and dbo.Item.UnitOfMeasure = 'TN' 
                 and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

GROUP BY 	 dbo.Job.CompanyJobId, fMeasure, dbo.Region.CompanyRegionID,
                 dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active

Now I need to inser another join like this:

dbo.Event ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid INNER JOIN

and I get error:

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

Not sure what this means. Thank you.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-18 : 12:41:03

You will need to use table aliases if you are joining to the same table multiple times.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 12:55:17

[code]SELECT j.CompanyJobId, i.UnitOfMeasure, j.Name, j.ChangeDate,
r.CompanyRegionID, j.Active,
sum(case j.CompanySourceTypeId WHEN 'MA' then pe.AlternateQuantity ELSE 0 END) AS material,
sum(case st.CompanySourceTypeId WHEN 'PR' THEN pe.Quantity ELSE 0 END) AS production

FROM dbo.job j
inner join dbo.Event e ON j.JobGuid = e.JobGuid
inner join dbo.ProductionEvent pe on e.EventGuid = pe.EventGuid
left outer join dbo.Product p ON pe.ProductGuid = p.ProductGuid
left outer JOIN dbo.Item i ON e.ItemGuid = i.ItemGuid
inner JOIN dbo.Source s ON pe.SourceGuid = s.SourceGuid
inner JOIN dbo.SourceType st ON s.SourceTypeGuid = st.SourceTypeGuid
left OUTER JOIN dbo.Region r ON j.RegionGuid = r.RegionGuid
...similarly other joins
WHERE j.CompanyJobId = 3505048 and i.UnitOfMeasure = 'TN'
and(st.CompanySourceTypeId = 'PR' or st.CompanySourceTypeId = 'MA')

GROUP BY j.CompanyJobId, fMeasure,r.CompanyRegionID,
j.Name, j.ChangeDate, j.Active[/code]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-19 : 16:48:02

I am still a little bit confused usin aliases. Here is my current code:

SELECT		dbo.Job.JobGuid, e1.eventguid, dbo.ProductionEvent.EventGuid, 
   	        dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.ChangeDate, 
            		dbo.Region.CompanyRegionID, dbo.Job.Active,
           	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
                             sum(EmployeeLaborEvent.Hours) as hours
FROM               dbo.job 

             	         left outer join dbo.Event e1 ON dbo.Job.JobGuid = e1.JobGuid 
	                 left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
                         left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                         left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
                         inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
                   
			 left outer join dbo.Event e2 ON dbo.Job.JobGuid = e2.JobGuid 
                         left outer join dbo.EmployeeLaborEvent ON dbo.event.eventguid = dbo.Employeelaborevent.EventGuid 
			 
WHERE          dbo.Job.CompanyJobId = 3505048 
                        and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

Now, the problem I have is with table Event being used multiple times, I tried to seperate them using an E1 and an E2. Do I need to use aliases on all the tables or can I just use aliases on the tables that have multiple occurrences?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-19 : 16:54:37

As long as column names are unique you do not need to alias the table, however it is a good practice to always use table aliases as it tends to make your scripts easier to read.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-19 : 18:31:34

Ok, I think I understand (at least that is what I thought)

SELECT		dbo.Job.JobGuid, e1.eventguid, dbo.ProductionEvent.EventGuid, 
   	        dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.ChangeDate, 
            		dbo.Region.CompanyRegionID, dbo.Job.Active,
           	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
                             sum(EmployeeLaborEvent.Hours) as hours
FROM               dbo.job 

             	         left outer join dbo.Event e1 ON dbo.Job.JobGuid = e1.JobGuid 
	                 left outer join dbo.ProductionEvent on e1.EventGuid = dbo.ProductionEvent.EventGuid
                         left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                         left outer JOIN dbo.Item ON e1.Event.ItemGuid = dbo.Item.ItemGuid 
                         inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
                   
			 left outer join dbo.Event e2 ON dbo.Job.JobGuid = e2.JobGuid 
                         left outer join dbo.EmployeeLaborEvent ON e2.eventguid = dbo.Employeelaborevent.EventGuid 
			 
WHERE          dbo.Job.CompanyJobId = 3505048 
                        and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

Look at my code, I aliased the "Event" table (I thought), still it is giving me a syntax error:

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-19 : 19:00:00

Disregard my last post, got it working...

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:06:02

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources