List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 30 2004 8:18pm
Subject:Re: Shared column name
View as plain text  
You have to say which table's VendorID column you want to evaluate your 
WHERE clause against (just as the error message says). Try this

WHERE
  (VendorJobs.`VendorID` = 13)

As a shortcut , and to keep you from getting "typist's cramp", you only 
_need_ to specify the table name for columns that are NOT unique within 
the set of all columns in the tables you have joined into your query. For 
all uniquely named columns, you can drop the table specifier. 

Don't get me wrong, the way you are specifying your columns IS the 
**correct** method of being specific. I am merely suggesting a way for you 
to save yourself a few keystrokes and maybe a few typos along the way.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Stuart Felenstein <stuart4m@stripped> wrote on 08/30/2004 04:09:03 PM:

> Hi, 
> 
> I know what the deal is supposed to be but can't seem
> to fix it.
> Two tables, VendorID exists in both tables (neither
> are primary keys)
> 
> I'm getting a "VendorID in where clause is ambiguous" 
> Sometimes it actually processes the SQL weird. 
> I think this is because same column name in both
> tables, yet I have other same name in tables with no
> effect.  I tried alias on VendorJobs.VendorId AS Ven ,
> etc but it spit it back at me in the where clause with
> an unknown.
> 
> Here is the SQL:
> 
> SELECT 
>   `VendorJobs`.`JobID`,
>   `VendorJobs`.`Contact`,
>   `VendorJobs`.`Conmail`,
>   `StaIndTypes`.`CareerCategories`,
>   `StaUSCities`.`City`,
>   `USStates`.`States`,
>   `VendorJobs`.`AreaCode`,
>   `VendorJobs`.`PayRate1`,
>   `VendorJobs`.`PayRate2`,
>   `staTravelReq`.`TravelReq`,
>   `VendorJobs`.`VendorID`
> FROM
>   `VendorJobs`
>   INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry`
> = `StaIndTypes`.`CareerIDs`)
>   INNER JOIN `StaUSCities` ON
> (`VendorJobs`.`LocationState` =
> `StaUSCities`.`CityID`)
>   INNER JOIN `USStates` ON (`StaUSCities`.`StateID` =
> `USStates`.`StateID`)
>   INNER JOIN `staTravelReq` ON
> (`VendorJobs`.`TravelReq` =
> `staTravelReq`.`TravelReqID`),
>   `VendorSignUp`
> WHERE
>   (`VendorID` = 13)
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
Shared column nameStuart Felenstein30 Aug
  • Re: Shared column nameMartijn Tonies30 Aug
  • Re: Shared column nameMark C. Stafford30 Aug
  • Re: Shared column nameSGreen30 Aug
    • Re: Shared column nameStuart Felenstein30 Aug