Hi all,
I hope someone can helps me....
I have a problem detecting a sum of cartesian product of tables.
----------------------------
Test case:
//CREATE TABLES
create table t1 (
id serial,
field_1 integer);
create table t2 (
id serial,
field_1 integer);
create table t3 (
id serial,
field_1 integer);
create table t4 (
id serial,
field_1 integer);
//FILL TABLES
insert into t1 (field_1) select cast(random()*10 as integer) from
generate_series(1,10);
insert into t2 (field_1) select cast(random()*10 as integer) from
generate_series(1,10);
insert into t3 (field_1) select cast(random()*10 as integer) from
generate_series(1,10);
insert into t4 (field_1) select cast(random()*10 as integer) from
generate_series(1,10);
--------------------
Example: i have 4 tables with fields, i would detect which combination
of field_1 in any table exceed a value (eg. 35).
Simple, ugly & slow but simple:
select * from t1, t2,t3,t4 where t1.field_1 + t2.field_1 + t3.field_1 +
t4.field_1 >35
It works.
Now my question: i would determine which combination on field_1 of
t1,t2,t3 plus a combination(any) of 2 records on field_1 of t4,
exceeds a value (eg. 35)
It should be something like t1.field_1 + t2.field_1 + t3.field_1 + (
any combination of 2 records of t4.field_1) > 35
Suppose i have these records in tables (field_1), for simple explain of
my problem:
t1 = 1
t2 = 5
t3 = 4
t4 = 1,3,4
the combination of 2 record on t4.field_1 should be:
1+5+4 + ( 1+3)
1+5+4 + ( 1+4)
1+5+4 + ( 3+1)
1+5+4 + ( 3+4)
1+5+4 + ( 4+1)
1+5+4 + ( 4+3)
How to do it???
This is a static test case with a static (2 records) problem, in my
production db it could be any combination (2,3,4,5+ records ) of field_1
of any table.
Hope I was clear,
Best regards and thanks in advance,
Agharta