Date: Tue, 18 Oct 2005 21:35:44 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Matthew Peter <survivedsushi@yahoo.com>
Cc: paperinik 100 <paperinik_100@hotmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: problems with array
Message-ID: <20051019033544.GA66012@winnie.fuhr.org>
References: <BAY108-F2573522163DF0C1FC43D0DD27C0@phx.gbl>
	<20051019030948.80709.qmail@web35201.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20051019030948.80709.qmail@web35201.mail.mud.yahoo.com>

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