Re: Filter tables

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