Re: query from a list of ids

You can try this one.

SELECT
table2.*
FROM
(SELECT string_to_array(ids, ', ') FROM table1 WHERE name =
'Peter') AS a(a),
(SELECT generate_series(1,array_upper(string_to_array(ids,
', '),1)+1,1)FROM table1 WHERE name = 'Peter') c(n),
table2
WHERE
table2.id = a[c.n]

finecur wrote:
> Hi,
>
> Here is my first table:
>
> Table1
>
> name| ids
> -------------------------
> Peter| 2, 3, 4, 5
> Jack| 100, 34, 3
>
> Both name and ids are in text format.
>
> Here is my second table
>
> Table2
>
> id | Flag | Title
> ---------------------
> 2 | Red | good
> 3 | Blue | poor
> 4 | Green| middle
>
> id is in integer (serial) format.
>
> I would like to list all the rows in table 2 where the id is in the
> ids field of peter. So I did
>
> select * from tables where id in (select ids from table1 where
> name='Peter')
>
> It did not work. How can I do the query?
>
> Thanks,
>
> ff
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>