Re: problems with array

On Tue, Oct 18, 2005 at 08:09:48PM -0700, Matthew Peter wrote:
> Not sure if you got this figured out but I think
>
> SELECT * from tb_cat WHERE id IN (SELECT
> array_to_string(cat,',') as cat FROM tb_array WHERE
> id=1);
>
> is what your looking for?

I doubt it, considering that it doesn't work :-(

SELECT * from tb_cat WHERE id IN (SELECT
array_to_string(cat,',') as cat FROM tb_array WHERE
id=1);

id | desc
----+------
(0 rows)

This might do the trick:

SELECT c.*
FROM tb_cat AS c, tb_array AS a
WHERE c.id = ANY (a.cat) AND a.id = 1;

id | desc
----+-------
10 | cat10
20 | cat20
(2 rows)

Or if you prefer the explicit join syntax:

SELECT c.*
FROM tb_cat AS c JOIN tb_array AS a ON c.id = ANY (a.cat)
WHERE a.id = 1;

id | desc
----+-------
10 | cat10
20 | cat20
(2 rows)

--
Michael Fuhr