0

Seems I'm rusty at my sql and could use some help.

I'm running into an issue on the search query below:

SELECT COUNT(1) as totalITCount FROM it_table 
 JOIN i_table on i_table.key = it_table.item_key 
   WHERE 
  (key IN (select item_key from it_table where LOWER(it_table.resolution_feild) LIKE ? OR it_table.resolution_field = '' group by item_key) )

and the error coming from the operation is:

nested exception is org.postgresql.util.PSQLException: ERROR: column reference "key" is ambiguous.

I'm not sure what this means. I have another field present in both tables and in that case I "oddly" DON'T see this error. I'd think if anything having a field in two places would make the operation ambiguous, so I'm even more confused.

Thoughts?

Jamiu S.
  • 5,257
  • 5
  • 12
  • 34
Drew L. Facchiano
  • 283
  • 3
  • 5
  • 12
  • 1
    If `key` field exists in both tables, you have to use with table name. – PM 77-1 Oct 14 '22 at 18:28
  • 1
    Does this answer your question? [Query error with ambiguous column name in SQL](https://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql) – PM 77-1 Oct 14 '22 at 18:29
  • Does this answer your question? [SQL column reference "id" is ambiguous](https://stackoverflow.com/questions/9821121/sql-column-reference-id-is-ambiguous) – TylerH Oct 20 '22 at 13:44

1 Answers1

1

Last line references key without specifying table.

  (key IN....

should either be

  (i_table.key IN....

or

  (it_table.key IN....
Damir Hadnadjev
  • 36
  • 1
  • 3