Another Join Problem

Another Join Problem

am 02.10.2009 17:53:38 von Victor Subervi

--00032557f4ba6c36260474f5c4dc
Content-Type: text/plain; charset=ISO-8859-1

Hi;
I get the following error:

*SQL query:*

SELECT ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

*MySQL said:*
#1052 - Column 'ID' in field list is ambiguous

Please note the error is about ambiguity. "products" has an ID field and so
does "categories". If I run the statement taking out the "ID" from the
select, it runs. So, where is the ambiguity??
TIA,
V

--00032557f4ba6c36260474f5c4dc--

Re: Another Join Problem

am 02.10.2009 18:03:01 von David Giragosian

--001636d34a3e034dd50474f5e620
Content-Type: text/plain; charset=UTF-8

On Fri, Oct 2, 2009 at 10:53 AM, Victor Subervi wrote:

> Hi;
> I get the following error:
>
> *SQL query:*
>
> SELECT ID, Item
> FROM products
> JOIN categories ON categories.ID = products.Category
> LIMIT 0 , 30;
>
> *MySQL said:*
> #1052 - Column 'ID' in field list is ambiguous
>
> Please note the error is about ambiguity. "products" has an ID field and so
> does "categories". If I run the statement taking out the "ID" from the
> select, it runs. So, where is the ambiguity??
> TIA,
> V


Just prefix the ID with either table name like products.ID or categories.ID.

David

--001636d34a3e034dd50474f5e620--

Re: Another Join Problem

am 02.10.2009 18:03:27 von John Daisley

Qualify the column names. EG

SELECT categories.ID, products.Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

Regards

John Daisley



> Hi;
> I get the following error:
>
> *SQL query:*
>
> SELECT ID, Item
> FROM products
> JOIN categories ON categories.ID = products.Category
> LIMIT 0 , 30;
>
> *MySQL said:*
> #1052 - Column 'ID' in field list is ambiguous
>
> Please note the error is about ambiguity. "products" has an ID field and
> so
> does "categories". If I run the statement taking out the "ID" from the
> select, it runs. So, where is the ambiguity??
> TIA,
> V
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Another Join Problem

am 02.10.2009 18:08:48 von Mark Goodge

Victor Subervi wrote:
> Hi;
> I get the following error:
>
> *SQL query:*
>
> SELECT ID, Item
> FROM products
> JOIN categories ON categories.ID = products.Category
> LIMIT 0 , 30;
>
> *MySQL said:*
> #1052 - Column 'ID' in field list is ambiguous
>
> Please note the error is about ambiguity. "products" has an ID field and so
> does "categories". If I run the statement taking out the "ID" from the
> select, it runs. So, where is the ambiguity??

The ambiguity is that the select clause doesn't know which table you're
referring to, since you're joining two of them that both have an 'ID'
field. This will work:

SELECT products.ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Another Join Problem

am 02.10.2009 18:13:45 von Shawn Green

Victor Subervi wrote:
> Hi;
> I get the following error:
>
> *SQL query:*
>
> SELECT ID, Item
> FROM products
> JOIN categories ON categories.ID = products.Category
> LIMIT 0 , 30;
>
> *MySQL said:*
> #1052 - Column 'ID' in field list is ambiguous
>
> Please note the error is about ambiguity. "products" has an ID field and so
> does "categories". If I run the statement taking out the "ID" from the
> select, it runs. So, where is the ambiguity??
> TIA,
> V
>

From which table did you want the ID value of? Did you want to know

SELECT categories.ID, item

or

SELECT products.ID, item

for your results? When there is a duplication like this, you must be
specific as MySQL can do many things but the ability to read the minds
of its DBA's is not one of them.

Respecfully,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Another Join Problem

am 02.10.2009 19:04:35 von Victor Subervi

--001636426873322bd70474f6c2b0
Content-Type: text/plain; charset=ISO-8859-1

Thanks
V

On Fri, Oct 2, 2009 at 11:08 AM, Mark Goodge wrote:

> Victor Subervi wrote:
>
>> Hi;
>> I get the following error:
>>
>> *SQL query:*
>>
>> SELECT ID, Item
>> FROM products
>> JOIN categories ON categories.ID = products.Category
>> LIMIT 0 , 30;
>>
>> *MySQL said:*
>> #1052 - Column 'ID' in field list is ambiguous
>>
>> Please note the error is about ambiguity. "products" has an ID field and
>> so
>> does "categories". If I run the statement taking out the "ID" from the
>> select, it runs. So, where is the ambiguity??
>>
>
> The ambiguity is that the select clause doesn't know which table you're
> referring to, since you're joining two of them that both have an 'ID' field.
> This will work:
>
> SELECT products.ID, Item
> FROM products
> JOIN categories ON categories.ID = products.Category
> LIMIT 0 , 30;
>
> Mark
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=victorsubervi@gmail.com
>
>

--001636426873322bd70474f6c2b0--