SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Column 'X' in IN/ALL/ANY subquery is ambiguous
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jswebsteve
Starting Member

United Kingdom
3 Posts

Posted - 11/29/2012 :  06:33:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 11/29/2012 :  06:42:54  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  06:43:16  Show Profile  Reply with Quote
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')
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/29/2012 :  06:46:25  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  06:47:47  Show Profile  Reply with Quote
Jim, for once, you got sniped not once, but twice! My condolences!!
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/29/2012 :  07:11:17  Show Profile  Reply with Quote
I have got to learn how to type!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

jswebsteve
Starting Member

United Kingdom
3 Posts

Posted - 11/29/2012 :  07:23:07  Show Profile  Reply with Quote
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')


Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  07:28:42  Show Profile  Reply with Quote
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

Edited by - sunitabeck on 11/29/2012 07:29:54
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/29/2012 :  07:32:16  Show Profile  Reply with Quote
Perhaps if you weren't typing so fast you wouldn't have missed that?!

Everyday I learn something that somebody else already knew
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  07:33:59  Show Profile  Reply with Quote
Sour grapes!
Go to Top of Page

jswebsteve
Starting Member

United Kingdom
3 Posts

Posted - 11/29/2012 :  07:41:16  Show Profile  Reply with Quote
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



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000