Column 'id' in field list is ambiguous when using IN clause

Question

I have written a query on three tables that share the common key named id. When the values are returned from the inner select statements,the outer select is not able to resolve the id. I looked at the posts in stackoverflow like the one below: [1052: Column 'id' in field list is ambiguous

It recommends that we use aliases to disambiguate the problem, however it does'nt seem to be working for me. Following is my query in mysql.

SELECT
  e2.`exchange_code` 
  e1.`id`,
  e1.`error_time`,
  e1.`last_update_time` 
FROM
  `exchange_error_details` e1,
  `exchange_products` e2
WHERE e1.`id` IN 
  (SELECT 
    id 
  FROM
    exchange_products) 
  AND e1.`id` NOT IN 
  (SELECT 
    id 
  FROM
    mdd_suppressed_keys) 
  AND e1.`clear_time` IS NULL

Edit:

exchange_error_details

id                 error_time   clear_time  last_update_time    clear_ref_ticker
ADF.10052.1.Q.FEED  2014-11-25 10:36:25 \N  2014-11-25 02:36:25 THD
ADF.10052.1.Q.FEED  2014-11-28 13:50:06 2014-11-28 13:50:37 2014-11-28 13:49:51 AMLP
ADF.10052.1.Q.FEED  2014-11-28 13:53:08 2014-11-28 13:53:38 2014-11-28 13:52:44 ONVO
ADF.10052.1.Q.FEED  2014-11-28 13:55:09 2014-11-28 13:56:10 2014-11-28 13:54:54 ACAD
ADF.10052.1.Q.FEED  2014-11-28 13:58:10 2014-11-28 13:58:41 2014-11-28 13:57:56 HABT

exchange_products:

 id               exchange_code iso_code    issue_type  
ADF.10052.1.Q.FEED  10052        ADF           1    
ADF.10052.1.T.FEED  10052        ADF           1    
ADF.10074.1.T.FEED  10074        ADF           1    
ADF.11099.1.T.FEED  11099        ADF           1    
ADF.11099.14.T.FEED 11099        ADF          14

mdd_suppressed_keys

id                  user    date
ADF.10052.1.Q.FEED  droy    2015-03-09 03:16:22
HAM.17.41.Q.FEED    droy    2015-03-09 03:16:26
HAM.17.41.T.FEED    droy    2015-03-09 03:16:30
IND.114.1.T.FEED    droy    2015-03-09 03:16:22
SWX.4.41.Q.FEED     droy    2015-03-09 03:16:22

Error Message

1 queries executed, 0 success, 1 errors, 0 warnings

Query: select e2.`exchange_code`, e1.`id`, e1.`error_time`, e1.`last_update_time` from `exchange_error_details` e1, `exchange_products`...

Error Code: 1052
Column 'id' in field list is ambiguous

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.388 sec

Why I wrote this query:

I wish to return only those values from the exchange_error_details table whose id field matches the one in the exchange_products table and exclude out the ones present in the mdd_suppressed_keys table.

Thanks!

You have a cross join here. Did you intend for that? You should use the modern `JOIN` operators instead of the old comma syntax. — siride, Mar 09 '15 at 14:39
When you say "not working", what error are you getting and where in the query is it giving the error? — siride, Mar 09 '15 at 14:39
could you show up to 5 records for `exchange_error_details` and `exchange_products` tables please? we can't see what is relations between those 2 tables. which columns must be bind to jojn tables? — Alex, Mar 09 '15 at 14:54
Fixed the typo. it should be exchange_products.Sorry abt that. — deb, Mar 09 '15 at 15:39

score 1 · Accepted Answer · answered Mar 09 '15 at 15:38

If I got your structure correct, your query must be LEFT JOINed :)

Try:

SELECT
  e2.`exchange_code` 
  e1.`id`,
  e1.`error_time`,
  e1.`last_update_time` 
FROM
  `exchange_error_details` e1
LEFT JOIN
  `exchange_products` e2
ON
  e1.id = e2.id
WHERE e1.`id` IN 
  (SELECT 
    id 
  FROM
    exchange_control_info) 
  AND e1.`id` NOT IN 
  (SELECT 
    id 
  FROM
    mdd_suppressed_keys) 
  AND e1.`clear_time` IS NULL

score 0 · Answer 2 · answered Mar 09 '15 at 14:34

0

Maybe something like this:

WHERE e1.`id` IN 
  (SELECT 
    exchange_control_info.id 
  FROM
    exchange_control_info) 
  AND e1.`id` NOT IN 
  (SELECT 
    mdd_suppressed_keys.id 
  FROM
    mdd_suppressed_keys) 
  AND e1.`clear_time` IS NULL

answered Mar 09 '15 at 14:34

Arion

31,011
10
70
88

Column 'id' in field list is ambiguous when using IN clause

2 Answers2