SELECT bigint[] from table, then lookup each item from bigint[] in another table-CodePudding

I have two tables:

PROJECT that contains the two columns - PROJECTNAME (text) and HOSTS (bigint[])

HOST that contains the three columns HOSTID (bigint) and IP (inet) and HOSTNAME (text)

I wish to write a single query that does the following:

SELECT hosts 
FROM project
WHERE projectname = 'Project test'

then for each bigint in HOSTS I wish to do something like this:

FOREACH hostid in HOSTS(bigint[])
SELECT x,y,x FROM HOST WHERE IP <<=inet '10.10.10.0/20'

I'm currently doing this in an inefficient way with multiple separate queries. I'd really like to understand how to combine them into one succinct query.

Thanks, I'm just keen to better understand how to build these queries.

CodePudding user response：

You can join using the ANY operator:

SELECT h.hostid, h.ip, h.hostname
FROM project p
  JOIN hosts h on h.hostid = ANY(p.hosts)
WHERE p.projectname = 'Project test'
  AND h.ip <<= inet '10.10.10.0/20';