selection with an array

[nofunnystuff]

The problem seems to be simple.

I have one table with a column as Array-typ where IDs of another table are stored in.

Now I want to use the array to select the elements of the second table.

I thought of a statement like

SELECT * FROM secondtable where id in (SELECT arrayCol FROM firsttable where id = 1 LIMIT 1)

but this does not work...

The goal is to do it compleatly in SQL no matter how.

Thanks for advice

 

[ceco]

you shouldn't have used arrays for this thing! it is done with intermediate table!!!

CREATE TABLE connection (id_tbl1 integer, id_tbl2 integer);

SELECT t2.* FROM tbl2 AS t2, connection AS c WHERE c.id_tbl1 = 1 AND t2.id = c.id_tbl2;
(you can include and the first table if needed)


for the arrays try something like (i'm not sure if it will work)
SELECT * FROM secondtable WHERE id = ANY (SELECT arrayCol FROM firsttable WHERE id = 1 LIMIT 1)