| Author |
 Topic  |
|
|
jswebsteve
Starting Member
United Kingdom
3 Posts |
 Posted - 11/29/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/29/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/29/2012 : 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 belowSELECT *
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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/29/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/29/2012 : 06:47:47
|
Jim, for once, you got sniped not once, but twice! My condolences!!  |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/29/2012 : 07:11:17
|
I have got to learn how to type!
Jim
Everyday I learn something that somebody else already knew |
|
|
|
jswebsteve
Starting Member
United Kingdom
3 Posts |
Posted - 11/29/2012 : 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 belowSELECT *
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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/29/2012 : 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 |
Edited by - sunitabeck on 11/29/2012 07:29:54 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/29/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/29/2012 : 07:33:59
|
Sour grapes!  |
|
|
|
jswebsteve
Starting Member
United Kingdom
3 Posts |
Posted - 11/29/2012 : 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
|
|
|
| |
Topic |
|
Column 'X' in IN/ALL/ANY subquery is ambiguous
