Column 'X' in IN/ALL/ANY subquery is ambiguous

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

General SQL Server Forums

New to SQL Server Programming

Column 'X' in IN/ALL/ANY subquery is ambiguous

Author

Topic

jswebsteve
Starting Member

3 Posts

Posted - 2012-11-29 : 06:33:02

I'm having trouble with an SQL query and wondered if you can help me. The original query which works is:
SELECT * orders_products WHERE orders_id IN(SELECT DISTINCT orders_id FROM orders WHERE ImportfromChinaRequested='0' and orders_id >'0').

However, if i try getting it to collect attributes as well, like this:

SELECT * FROM orders_products_attributes , orders_products WHERE orders_id IN(SELECT DISTINCT orders_id FROM orders WHERE ImportfromChinaRequested='0' and orders_id >'0')

I get the following error:
Column 'orders_id' in IN/ALL/ANY subquery is ambiguous

Any suggestions on a fix for this?

Thanks in advance
Steve

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-29 : 06:42:54

SELECT * FROM orders_products_attributes , orders_products WHERE GiveAnyOneOfTableReference.orders_id IN(SELECT DISTINCT orders_id FROM orders WHERE ImportfromChinaRequested='0' and orders_id >'0')

May be these two tables(orders_products_attributes , orders_products ) having same column name order_id......

--
Chandu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 06:43:16

The text of the error message does not seem like a T-SQL message. Are you using Microsoft SQL Server? In any case, you need to make a few changes to the query - see below

SELECT *
FROM   orders_products_attributes oa INNER JOIN
       orders_products op ON
		oa.order_id = op.order_id
WHERE  oa.orders_id IN (SELECT DISTINCT o.orders_id
                     FROM   orders o
                     WHERE  o.ImportfromChinaRequested = '0'
                            AND o.orders_id > '0')

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-29 : 06:46:25

Alias your table so that SQL knows which table orders_id you mean, something like

FROM orders_products_attributes opa
INNER JOIN orders_products op ON op.orders_id = opa.orders_id

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 06:47:47

Jim, for once, you got sniped not once, but twice! My condolences!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-29 : 07:11:17

I have got to learn how to type!

Jim

Everyday I learn something that somebody else already knew

jswebsteve
Starting Member

3 Posts

Posted - 2012-11-29 : 07:23:07

Thanks for the rapid replies!

I tried your query, but it failed with #1054 - Unknown column 'oa.order_id' in 'on clause'

I'm running Zend Server CE on a mac as a dev environment. Any error messages are those given either by phpMyAdmin, or by the error logs on the client side software.

quote:

Originally posted by sunitabeck

The text of the error message does not seem like a T-SQL message. Are you using Microsoft SQL Server? In any case, you need to make a few changes to the query - see below

SELECT *
FROM   orders_products_attributes oa INNER JOIN
       orders_products op ON
		oa.order_id = op.order_id
WHERE  oa.orders_id IN (SELECT DISTINCT o.orders_id
                     FROM   orders o
                     WHERE  o.ImportfromChinaRequested = '0'
                            AND o.orders_id > '0')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 07:28:42

Your column name is orders_id, not order_id - I had missed that:

SELECT *
FROM   orders_products_attributes oa INNER JOIN
       orders_products op ON
		oa.orders_id = op.orders_id
WHERE  oa.orders_id IN (SELECT DISTINCT o.orders_id
                     FROM   orders o
                     WHERE  o.ImportfromChinaRequested = '0'
                            AND o.orders_id > '0')

BTW, you may be using MySQL backend. This forum is for Microsoft SQL Server, so expertise on MySQL is likely to be far and few in-between. You may get better and faster responses on a MySQL forum or a generalized database forum such as dbforums.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-29 : 07:32:16

Perhaps if you weren't typing so fast you wouldn't have missed that?!

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 07:33:59

Sour grapes!

jswebsteve
Starting Member

3 Posts

Posted - 2012-11-29 : 07:41:16

Well that was a fast and accurate response anyway. Thanks so much. I really need to brush up on my sql knowledge.

quote:
Originally posted by sunitabeck
. You may get better and faster responses on a MySQL forum or a generalized database forum such as dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources