An error occurred while executing the query: Column 'id' in IN / ALL / ANY subquery is ambiguous enter image description here

SELECT item.* FROM item JOIN price ON price.item = item.id JOIN currency ON currency.id = price.currency WHERE `id` IN (SELECT `item_id` FROM `item_code` WHERE `number` IN (SELECT `id` FROM `number` WHERE `mark`='3')) ORDER BY price.price * currency.course LIMIT 20
  • you have duplicate entries likely to make a distinct request. - Naumov
  • Such as? (Thank you) - Arzek 5:03 pm
  • SELECT DISTINCT id `FROM number WHERE mark = '3'` so try it but I don’t browse in sql I just php - Naumov
  • I do not think that the problem is this. - Arnial pm

1 answer 1

Judging by the request, the id field is in at least 2 tables (item and currency)

In WHERE you need to specify by id from which table you want to filter.

If it is an item then the request should be like this:

  SELECT item.* FROM item JOIN price ON price.item = item.id JOIN currency ON currency.id = price.currency WHERE item.`id` IN (SELECT `item_id` FROM `item_code` WHERE `number` IN (SELECT `id` FROM `number` WHERE `mark`='3')) ORDER BY price.price * currency.course LIMIT 20
  • Thanks, it turned out) - Arzek