CODEWITHSUNDEEP
sqlpostgresqljoin
i.krivosheev
i.krivosheev

Reputation: 397

SQL Join with operator IN

I'm having a problem joining two tables using IN. Example:

with nodes(node_id, mpath) as (
 SELECT node_id, drugs_cls_node_view.mpath
 FROM drugs_cls_entries_view
   inner join drugs_cls_node_view on drugs_cls_node_view.id = node_id
 WHERE mnn_id in (13575)
)
select DISTINCT n.node_id, drugs_cls_node_view.*  
from nodes n
  inner join drugs_cls_node_view
  on drugs_cls_node_view.id in (array_replace(string_to_array(n.mpath, '/'), '', '0')::bigint[])

I get the exception:

ERROR: operator does not exist: bigint = bigint[]

Upvotes: 0

Views: 650

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

With 

on drugs_cls_node_view.id in 
  (array_replace(string_to_array(n.mpath, '/'), '', '0')::bigint[])

you look for the ID in a set containing just one element. This element is an array. The ID can never equal the array, hence the error.

You must unnest the array to have single values to compare with:

on drugs_cls_node_view.id in 
  (select(unnest(array_replace(string_to_array(n.mpath, '/'), '', '0')::bigint[])))

Or use ANY on the array instead of IN:

on drugs_cls_node_view.id = ANY
  (array_replace(string_to_array(n.mpath, '/'), '', '0')::bigint[])

There may be syntactical errors in my code, as I am no postgres guy, but it should do with maybe a little correction here or there :-)

Upvotes: 1

Related Questions