0

I have a complex query and i saw many questions in stackoverflow like this but didnt found what is the problem with my query, i'm getting this error

"Integrity constraint violation: 1052 Column 'orders_id' in field list is ambiguous'"

when i try to execute

 SELECT order_process_status.process_status_id,
   orders.orders_due_date,
   order_process_status.process_status_class,
   order_process_status_description.process_status_title,
   Group_concat(DISTINCT Concat(orders.orders_id, '||',
   orders.order_id_type, '||',
   IF(orders.orders_due_date IS NULL, '', orders.orders_due_date), '||',
   IF(orders.orders_due_date IS NULL,
   '', IF(orders.orders_due_date < '2016-10-06', 'due',
   IF(orders.orders_due_date =
   '2016-10-06', 'today', IF(orders.orders_due_date = '2016-10-06' +
   INTERVAL 1 day
   , 'tomorrow', ''))))) ORDER BY orders_id DESC) AS orders_id
FROM   order_process_status
   LEFT JOIN order_process_status_description
          ON order_process_status.process_status_id =
                       order_process_status_description.process_status_id
             AND order_process_status_description.site_language_id = 1
   LEFT JOIN orders
          ON order_process_status.process_status_id =
             orders.orders_status_id
             AND orders.order_approval_status = '0'
             AND orders.orders_date_finished > Date_sub(Now(),
                                               INTERVAL 1 month)
   LEFT JOIN orders_products
     ON orders_products.orders_id = orders.orders_id
WHERE  orders.store_id = 1
   AND ( orders.admin_id IS NULL
          OR orders.admin_id = 1 )
   AND orders_products.template_type != -1
   AND order_process_status.status_type = '0'
   AND dashboard_visibility = '1'
   AND process_status_complete != 1
   AND process_status_cancel != 1
GROUP  BY order_process_status.process_status_id
ORDER  BY process_sort_order

I'm getting this problem when i try to add this left join

LEFT JOIN orders_products ON orders_products.orders_id = orders.orders_id

What i'm doing wrong here? please suggest me one way

Anju
  • 502
  • 1
  • 6
  • 20
  • 2
    I cannot detailedly look at your statement but this error occurs only when you have same column name in two different tables, specify which table it belongs to will be helpful –  Oct 06 '16 at 06:41
  • 2
    Use `ORDER BY orders.orders_id DESC` inside your call to `GROUP_CONCAT`. – Tim Biegeleisen Oct 06 '16 at 06:41
  • 1
    it worked @TimBiegeleisen. Thanks a lot!!!!!. i was looking for this issue since long. – Anju Oct 06 '16 at 06:44
  • I know that it is a duplicate question @TimBiegeleisen. But i cant even find what i did wrong in my query – Anju Oct 06 '16 at 06:52
  • You referred to column `orders_id`, but there were probably several tables in scope which had a column by that name. By prefixing with the table name/alias, you resolved this to refer to just one known column. – Tim Biegeleisen Oct 06 '16 at 06:53
  • Yes @TimBiegeleisen. No problem for the duplicate tag. Thanks for the answer – Anju Oct 06 '16 at 06:55
  • I actually didn't mark this as duplicate, I voted to close because it just has a simple typo. You can thank @drew for the duplicate mark :-) – Tim Biegeleisen Oct 06 '16 at 06:56
  • If it brought any negative vibes, then definitely hold Tim responsible :p – Drew Oct 06 '16 at 07:00
  • haha. no problem @Drew :) – Anju Oct 06 '16 at 07:04

0 Answers0