Re: Filter tables

On Nov 12, 2007, at 9:43 AM, Reg Me Please wrote:

> Hi all.
>
> I have this sample setup:
>
> CREATE table t1 ( t text, id int );
> CREATE TABLE f1 ( t text );
>
> INSERT INTO t1 VALUES
>   ( 'field1',1 ),
>   ( 'field2',1 ),
>   ( 'field3',1 ),
>   ( 'field1',2 ),
>   ( 'field3',3 )
> ;
>
> INSERT INTO f1 VALUES
>   ( 'field1' ),
>   ( 'field2' )
> ;
>
> What I'd need to do is to "filter" t1 against f1 to get only the rows
> ( 'field1',1 ) and ( 'field2',1 ).
> Of course both t1 and f1 don't have a defined number of rows,
> though usually
> t1 should be much bigger that f1.
>
> I have a rather complex solution in mind with loops in a plpgsql
> function and
> am wondering whether there is one simpler.

You're really going to need to go into some more detail about what
you're actually trying to do here.  The following query will get your
requested results, but I'm not sure it's really what you want:

SELECT t1.t, t1.id
FROM t1, f1
WHERE t1.t = f1.t and t1.id = 1;

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com