Error: The column prefix 'Contacts' does not...

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)

Error: The column prefix 'Contacts' does not...

Author

Topic

acbarberi
Starting Member

11 Posts

Posted - 2008-08-17 : 07:46:18

How in the world do I fix this??

Error:

Msg 107, Level 15, State 1, Line 1
The column prefix 'Contacts' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'BIND_E_StudSpacings' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'BIND_E_WallHeights' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'BIND_E_FloorJoistDepths' does not match with a table name or alias name used in the query.


1    SELECT Estimates.*, BIND_COMPANY.*, BIND_E_StoreNumbers.*, BIND_E_PricingPeriods.*, BIND_E_ProductNames.*,
2    BIND_E_PlanNames.*, Contacts.*, BIND_E_StudSpacings.*, BIND_E_WallHeights.*, BIND_E_FloorJoistDepths.*,
3    BIND_E_WindowSuppliers.*, BIND_E_FoundationTypes.*
4    FROM Estimates INNER JOIN
5        BIND_COMPANY ON Estimates.CustomerID = BIND_COMPANY.ID INNER JOIN
6        BIND_E_StoreNumbers ON Estimates.StoreNumberID = BIND_E_StoreNumbers.ID INNER JOIN
7        BIND_E_PricingPeriods ON Estimates.PricingPeriodID = BIND_E_PricingPeriods.ID INNER JOIN
8        Contacts c1 ON Estimates.DesignedByID = c1.ID INNER JOIN
9        BIND_E_ProductNames ON Estimates.ProductNameID = BIND_E_ProductNames.ID INNER JOIN
10       BIND_E_PlanNames ON Estimates.PlanNameID = BIND_E_PlanNames.ID INNER JOIN
11       Contacts c2 ON Estimates.StoreSalesRepID = c2.ID INNER JOIN
12       Contacts c3 ON Estimates.EstimatorID = c3.ID INNER JOIN
13       BIND_E_StudSpacings bess1 ON Estimates.InteriorStudSpacingID = bess1.ID INNER JOIN
14       BIND_E_StudSpacings bess2 ON Estimates.ExteriorStudSpacingID = bess2.ID INNER JOIN
15       BIND_E_WallHeights bewh1 ON Estimates.FirstFloorWallHeightID = bewh1.ID INNER JOIN
16       BIND_E_WallHeights bewh2 ON Estimates.SecondFloorWallHeightID = bewh2.ID INNER JOIN
17       BIND_E_FoundationTypes ON Estimates.FoundationTypeID = BIND_E_FoundationTypes.ID INNER JOIN
18       BIND_E_FloorJoistDepths befjd1 ON Estimates.CrawlSpaceFloorJoistDepthID = befjd1.ID INNER JOIN
19       BIND_E_FloorJoistDepths befjd2 ON Estimates.FirstFloorJoistDepthID = befjd2.ID INNER JOIN
20       BIND_E_FloorJoistDepths befjd3 ON Estimates.SecondFloorJoistDepthID = befjd3.ID INNER JOIN
21       BIND_E_WindowSuppliers ON Estimates.WindowSupplierID = BIND_E_WindowSuppliers.ID
22   ORDER BY BIND_COMPANY.RName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-17 : 08:35:31

[code]SELECT Estimates.*, BIND_COMPANY.*, BIND_E_StoreNumbers.*, BIND_E_PricingPeriods.*, BIND_E_ProductNames.*,
2 BIND_E_PlanNames.*, {c1/c2/c3}.*, {bess1/bess2}.*, {bewh1/bewh2}.*, {befjd1/befjd2/befjd3}.*,
3 BIND_E_WindowSuppliers.*, BIND_E_FoundationTypes.*
4 FROM Estimates INNER JOIN
5 BIND_COMPANY ON Estimates.CustomerID = BIND_COMPANY.ID INNER JOIN
6 BIND_E_StoreNumbers ON Estimates.StoreNumberID = BIND_E_StoreNumbers.ID INNER JOIN
7 BIND_E_PricingPeriods ON Estimates.PricingPeriodID = BIND_E_PricingPeriods.ID INNER JOIN
8 Contacts c1 ON Estimates.DesignedByID = c1.ID INNER JOIN
9 BIND_E_ProductNames ON Estimates.ProductNameID = BIND_E_ProductNames.ID INNER JOIN
10 BIND_E_PlanNames ON Estimates.PlanNameID = BIND_E_PlanNames.ID INNER JOIN
11 Contacts c2 ON Estimates.StoreSalesRepID = c2.ID INNER JOIN
12 Contacts c3 ON Estimates.EstimatorID = c3.ID INNER JOIN
13 BIND_E_StudSpacings bess1 ON Estimates.InteriorStudSpacingID = bess1.ID INNER JOIN
14 BIND_E_StudSpacings bess2 ON Estimates.ExteriorStudSpacingID = bess2.ID INNER JOIN
15 BIND_E_WallHeights bewh1 ON Estimates.FirstFloorWallHeightID = bewh1.ID INNER JOIN
16 BIND_E_WallHeights bewh2 ON Estimates.SecondFloorWallHeightID = bewh2.ID INNER JOIN
17 BIND_E_FoundationTypes ON Estimates.FoundationTypeID = BIND_E_FoundationTypes.ID INNER JOIN
18 BIND_E_FloorJoistDepths befjd1 ON Estimates.CrawlSpaceFloorJoistDepthID = befjd1.ID INNER JOIN
19 BIND_E_FloorJoistDepths befjd2 ON Estimates.FirstFloorJoistDepthID = befjd2.ID INNER JOIN
20 BIND_E_FloorJoistDepths befjd3 ON Estimates.SecondFloorJoistDepthID = befjd3.ID INNER JOIN
21 BIND_E_WindowSuppliers ON Estimates.WindowSupplierID = BIND_E_WindowSuppliers.ID
22 ORDER BY BIND_COMPANY.RName[/code]

Change the aliases to any one of the ones given above (i'm not sure which one you want so have given all seperated by '/')

acbarberi
Starting Member

11 Posts

Posted - 2008-08-18 : 10:57:43

SELECT Estimates.*, BIND_COMPANY.*, BIND_E_StoreNumbers.*, BIND_E_PricingPeriods.*, BIND_E_ProductNames.*,
BIND_E_PlanNames.*, BIND_E_WindowSuppliers.*, BIND_E_FoundationTypes.*, c1.*, c2.*, c3.*, bess1.*,
bess2.*, bewh1.*, bewh2.*, befjd1.*, befjd2.*, befjd3.*
FROM Estimates INNER JOIN
BIND_COMPANY ON Estimates.CustomerID = BIND_COMPANY.ID INNER JOIN
BIND_E_StoreNumbers ON Estimates.StoreNumberID = BIND_E_StoreNumbers.ID INNER JOIN
BIND_E_PricingPeriods ON Estimates.PricingPeriodID = BIND_E_PricingPeriods.ID INNER JOIN
Contacts c1 ON Estimates.DesignedByID = c1.ID INNER JOIN
Contacts c2 ON Estimates.StoreSalesRepID = c2.ID INNER JOIN
Contacts c3 ON Estimates.EstimatorID = c3.ID INNER JOIN
BIND_E_ProductNames ON Estimates.ProductNameID = BIND_E_ProductNames.ID INNER JOIN
BIND_E_PlanNames ON Estimates.PlanNameID = BIND_E_PlanNames.ID INNER JOIN
BIND_E_StudSpacings bess1 ON Estimates.InteriorStudSpacingID = bess1.ID INNER JOIN
BIND_E_StudSpacings bess2 ON Estimates.ExteriorStudSpacingID = bess2.ID INNER JOIN
BIND_E_WallHeights bewh1 ON Estimates.FirstFloorWallHeightID = bewh1.ID INNER JOIN
BIND_E_WallHeights bewh2 ON Estimates.SecondFloorWallHeightID = bewh2.ID INNER JOIN
BIND_E_FoundationTypes ON Estimates.FoundationTypeID = BIND_E_FoundationTypes.ID INNER JOIN
BIND_E_FloorJoistDepths befjd1 ON Estimates.CrawlSpaceFloorJoistDepthID = befjd1.ID INNER JOIN
BIND_E_FloorJoistDepths befjd2 ON Estimates.FirstFloorJoistDepthID = befjd2.ID INNER JOIN
BIND_E_FloorJoistDepths befjd3 ON Estimates.SecondFloorJoistDepthID = befjd3.ID INNER JOIN
BIND_E_WindowSuppliers ON Estimates.WindowSupplierID = BIND_E_WindowSuppliers.ID
ORDER BY BIND_COMPANY.RName

Ok I changed it to be like this. Is this correct?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-18 : 11:08:43

FYI: it's a lot faster just to run the query and see if the changes worked rather then waiting for someone to tell you if it was correct! Also you might want to look into standardizing some of this data, you have a awful lot of joins here.


 SELECT 
Estimates.*
, BIND_COMPANY.*
, BIND_E_StoreNumbers.*
, BIND_E_PricingPeriods.*
, BIND_E_ProductNames.*
, BIND_E_PlanNames.*
, BIND_E_WindowSuppliers.*
, BIND_E_FoundationTypes.*
, c1.*
, c2.*
, c3.*
, bess1.*
, bess2.*
, bewh1.*
, bewh2.*
, befjd1.*
, befjd2.*
, befjd3.*
FROM 
	Estimates 
INNER JOIN
	BIND_COMPANY 
ON		Estimates.CustomerID = BIND_COMPANY.ID 
INNER JOIN
	BIND_E_StoreNumbers 
ON		Estimates.StoreNumberID = BIND_E_StoreNumbers.ID 
INNER JOIN
	BIND_E_PricingPeriods 
ON		Estimates.PricingPeriodID = BIND_E_PricingPeriods.ID 
INNER JOIN
	Contacts c1 
ON		Estimates.DesignedByID = c1.ID 
INNER JOIN
	Contacts c2 
ON		Estimates.StoreSalesRepID = c2.ID 
INNER JOIN
	Contacts c3 
ON		Estimates.EstimatorID = c3.ID 
INNER JOIN
	BIND_E_ProductNames 
ON		Estimates.ProductNameID = BIND_E_ProductNames.ID 
INNER JOIN
	BIND_E_PlanNames 
ON		Estimates.PlanNameID = BIND_E_PlanNames.ID 
INNER JOIN
	BIND_E_StudSpacings bess1 
ON		Estimates.InteriorStudSpacingID = bess1.ID 
INNER JOIN
	BIND_E_StudSpacings bess2 
ON		Estimates.ExteriorStudSpacingID = bess2.ID 
INNER JOIN
	BIND_E_WallHeights bewh1 
ON		Estimates.FirstFloorWallHeightID = bewh1.ID 
INNER JOIN
	BIND_E_WallHeights bewh2 
ON		Estimates.SecondFloorWallHeightID = bewh2.ID 
INNER JOIN
	BIND_E_FoundationTypes 
ON		Estimates.FoundationTypeID = BIND_E_FoundationTypes.ID 
INNER JOIN
	BIND_E_FloorJoistDepths befjd1 
ON		Estimates.CrawlSpaceFloorJoistDepthID = befjd1.ID 
INNER JOIN
	BIND_E_FloorJoistDepths befjd2 
ON		Estimates.FirstFloorJoistDepthID = befjd2.ID 
INNER JOIN
	BIND_E_FloorJoistDepths befjd3 
ON		Estimates.SecondFloorJoistDepthID = befjd3.ID 
INNER JOIN
	BIND_E_WindowSuppliers 
ON		Estimates.WindowSupplierID = BIND_E_WindowSuppliers.ID
ORDER BY BIND_COMPANY.RName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 11:41:59

quote:
Originally posted by acbarberi

SELECT Estimates.*, BIND_COMPANY.*, BIND_E_StoreNumbers.*, BIND_E_PricingPeriods.*, BIND_E_ProductNames.*,
BIND_E_PlanNames.*, BIND_E_WindowSuppliers.*, BIND_E_FoundationTypes.*, c1.*, c2.*, c3.*, bess1.*,
bess2.*, bewh1.*, bewh2.*, befjd1.*, befjd2.*, befjd3.*
FROM Estimates INNER JOIN
BIND_COMPANY ON Estimates.CustomerID = BIND_COMPANY.ID INNER JOIN
BIND_E_StoreNumbers ON Estimates.StoreNumberID = BIND_E_StoreNumbers.ID INNER JOIN
BIND_E_PricingPeriods ON Estimates.PricingPeriodID = BIND_E_PricingPeriods.ID INNER JOIN
Contacts c1 ON Estimates.DesignedByID = c1.ID INNER JOIN
Contacts c2 ON Estimates.StoreSalesRepID = c2.ID INNER JOIN
Contacts c3 ON Estimates.EstimatorID = c3.ID INNER JOIN
BIND_E_ProductNames ON Estimates.ProductNameID = BIND_E_ProductNames.ID INNER JOIN
BIND_E_PlanNames ON Estimates.PlanNameID = BIND_E_PlanNames.ID INNER JOIN
BIND_E_StudSpacings bess1 ON Estimates.InteriorStudSpacingID = bess1.ID INNER JOIN
BIND_E_StudSpacings bess2 ON Estimates.ExteriorStudSpacingID = bess2.ID INNER JOIN
BIND_E_WallHeights bewh1 ON Estimates.FirstFloorWallHeightID = bewh1.ID INNER JOIN
BIND_E_WallHeights bewh2 ON Estimates.SecondFloorWallHeightID = bewh2.ID INNER JOIN
BIND_E_FoundationTypes ON Estimates.FoundationTypeID = BIND_E_FoundationTypes.ID INNER JOIN
BIND_E_FloorJoistDepths befjd1 ON Estimates.CrawlSpaceFloorJoistDepthID = befjd1.ID INNER JOIN
BIND_E_FloorJoistDepths befjd2 ON Estimates.FirstFloorJoistDepthID = befjd2.ID INNER JOIN
BIND_E_FloorJoistDepths befjd3 ON Estimates.SecondFloorJoistDepthID = befjd3.ID INNER JOIN
BIND_E_WindowSuppliers ON Estimates.WindowSupplierID = BIND_E_WindowSuppliers.ID
ORDER BY BIND_COMPANY.RName

Ok I changed it to be like this. Is this correct?