Обсуждение: 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