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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
