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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
