[ale] Re: foreign key?

Ken Kennedy kkennedy at kenzoid.com
Fri Jan 25 13:44:39 EST 2002


On Fri, Jan 25, 2002 at 01:13:45PM -0500, David S. Jackson wrote:

> Here's another problem:  I think I'm doing an unintentional
> ambiguous select:
> 
> mysql> select Item.Item_Description, Item.Retail_Value,
> Item.Bid_Description, Contributors.Name from Item, Contributors
> where Contributor_ID > 1;
> 
> I get the error:
> 
> ERROR 1052: Column: 'Contributor_ID' in where clause is ambiguous

Well, I believe (I don't use MySQL, just going from straight SQL
syntax) that the issue causing the error is that the SQL parser
doesn't know which table's 'Contributor_ID > 1' you're referring
to. (ie, either say Item.Contributor_ID > 1, or
Contributors.Contributor_ID > 1). However, you're still going to not
get back what you want, b/c you presently don't have the tables joined
properly (again, assumptions about MySQL syntax).

You want something like 

select Item.Item_Description, Item.Retail_Value,
Item.Bid_Description, Contributors.Name from Item, Contributors
where Contributors.Contributor_ID > 1 and
Item.Contributor_ID =  Contributors.Contributor_ID

or 

select Item.Item_Description, Item.Retail_Value,
Item.Bid_Description, Contributors.Name from 
Item JOIN Contributor USING (Contributor_ID)

Otherwise, you get what's called a cartesian product, each row of
contributors joined to each row of items.

The USING clause may not be necessary, depending on MySQL's parser (I
imagine I could look it up, couldn't I...*grin*)

> So, looking again at my definitions for Contributor_ID in both
> tables, I see they aren't exactly alike.  Is there a way I can
> make Contributor_ID in Item refer back to the Index
> (Contributor_ID) in Contributors?  I gather that just making the
> data types the same is not enough?

When you say the definitions aren't exactly alike, are you saying that
the datatypes don't exactly match? (CHAR vs. VCHAR, or INT
vs. TINYINT, or something like that...). Again, some MySQL person'll
be able to answer definitively (or you can just dink with it), but I
imagine the parser is smart enough to coerce one of the datatypes and
allow the join.

Ken


 PGP signature




More information about the Ale mailing list