Message-ID: <550AF9A7.2090404@gmail.com>
Date: Thu, 19 Mar 2015 17:30:31 +0100
From: agharta <agharta82@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.3.0
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Detect which sum of cartesian product (+ any combination of n records
 in tables) exceeds a value
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql