Thread: Filter tables

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.

Thanks a lot.

--
Reg me Please

Hi,

Le lundi 12 novembre 2007, Reg Me Please a écrit :
> What I'd need to do is to "filter" t1 against f1 to get only the rows
> ( 'field1',1 ) and ( 'field2',1 ).

select * from t1 natural join f1 where t1.id = 1;
   t    | id
--------+----
 field1 |  1
 field2 |  1
(2 lignes)


I'm not sure about how you wanted to filter out the ('field1', 2) row of table
t1, so used the where t1.id = 1 restriction.

Hope this helps,
--
dim

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

Il Monday 12 November 2007 17:05:18 Dimitri Fontaine ha scritto:
> Hi,
>
> Le lundi 12 novembre 2007, Reg Me Please a écrit :
> > What I'd need to do is to "filter" t1 against f1 to get only the rows
> > ( 'field1',1 ) and ( 'field2',1 ).
>
> select * from t1 natural join f1 where t1.id = 1;
>    t    | id
> --------+----
>  field1 |  1
>  field2 |  1
> (2 lignes)
>
>
> I'm not sure about how you wanted to filter out the ('field1', 2) row of
> table t1, so used the where t1.id = 1 restriction.
>
> Hope this helps,

I think surely I've not been clean enough.

The rows in t1 should be seen as grouped by the field id. A group of such
rouws matches the filter f1 (made by two rows in my example) if I can find
all the values of f1 in the field t of that group.

So, in my example, in t1 the group of rows with id=2 (actually made by only
one row in my example) doesn't match the filter because it's lacking a row
with t='field2'.
In the same way the group of rows with id=3 won't match as they lack both
values that are in f1.

What I'd like to see as an output of the query/function is

 id
----
  1

as only the group with id=1 has both the values.
Of course, f1 could have any number of different values.

--
Reg me Please

Reg Me Please escreveu:
> Il Monday 12 November 2007 17:05:18 Dimitri Fontaine ha scritto:
>> Hi,
>>
>> Le lundi 12 novembre 2007, Reg Me Please a écrit :
>>> What I'd need to do is to "filter" t1 against f1 to get only the rows
>>> ( 'field1',1 ) and ( 'field2',1 ).
>> select * from t1 natural join f1 where t1.id = 1;
>>    t    | id
>> --------+----
>>  field1 |  1
>>  field2 |  1
>> (2 lignes)
>>
>>
>> I'm not sure about how you wanted to filter out the ('field1', 2) row of
>> table t1, so used the where t1.id = 1 restriction.
>>
>> Hope this helps,
>
> I think surely I've not been clean enough.
>
> The rows in t1 should be seen as grouped by the field id. A group of such
> rouws matches the filter f1 (made by two rows in my example) if I can find
> all the values of f1 in the field t of that group.
>
> So, in my example, in t1 the group of rows with id=2 (actually made by only
> one row in my example) doesn't match the filter because it's lacking a row
> with t='field2'.
> In the same way the group of rows with id=3 won't match as they lack both
> values that are in f1.
>
> What I'd like to see as an output of the query/function is
>
>  id
> ----
>   1
>
> as only the group with id=1 has both the values.
> Of course, f1 could have any number of different values.
>


Try:
SELECT DISTINCT t1.id FROM t1
  WHERE NOT EXISTS (SELECT f1.t FROM f1
                     WHERE NOT EXISTS (SELECT x1.t FROM t1 x1
                                        WHERE f1.t = x1.t
                                          AND t1.id = x1.id));

Osvaldo

Il Monday 12 November 2007 18:10:40 Osvaldo Rosario Kussama ha scritto:
> Try:
> SELECT DISTINCT t1.id FROM t1
>   WHERE NOT EXISTS (SELECT f1.t FROM f1
>                      WHERE NOT EXISTS (SELECT x1.t FROM t1 x1
>                                         WHERE f1.t = x1.t
>                                           AND t1.id = x1.id));
>
> Osvaldo

Nice, it seems to work. But I fear it won't with a longer f1 filter table.
Let me think about it.

--
Reg me Please