Re: Querying multiple tables - Mailing list pgsql-novice

Michael Glaesemann wrote:
>
> On Aug 1, 2007, at 7:33 , Paul Malherbe wrote:
>
>> 1) Owner Table - rcaowm - having own_code, owm_name
>> 2) Tenant Table - rcatnm - having tnm_code (owner's code), tnm_acno
>> (tenant's account number), tnm_name
>> 3) Owner Transactions - rcaowt - having owt_code (owner's code),
>> owt_date, owt_value
>> 4) Tenant Transactions - rcatnt - having tnt_code (owner's code),
>> tnt_acno (tenant's account number), tnt_date, tnt_value
>>
>> I want to produced a query giving the following data:
>>
>> Owner_Name, Tenant_Name, Sum_Owner_Transaction_Value,
>> Sum_Tenant_Transaction_Value
>>
>> Is there any way this can be achieved with a single select statement??
>
> Probably. However, it's hard to say with certainty as you've been
> inconsistent with your column naming. It's often best to include the
> actual table definitions (possibly with irrelevant columns removed),
> so people can see what you're dealing with.
>
> This is what I think you have:
>
> CREATE TABLE rcaowm
> (
>     own_code TEXT PRIMARY KEY
>     , owm_name TEXT NOT NULL UNIQUE
> );
>
> CREATE TABLE rcatnm
> (
>     tnm_acno TEXT PRIMARY KEY
>     , tnm_name TEXT NOT NULL UNIQUE
>     , tnm_code TEXT NOT NULL REFERENCES rcaown (own_code)
> );
>
> CREATE TABLE rcaowt
> (
>     owt_code TEXT REFERENCES rcaown (own_code)
>     , owt_date DATE NOT NULL
>     , owt_value NUMERIC NOT NULL
> );
>
> CREATE TABLE rcatnt
> (
>     tnt_code TEXT NOT NULL REFERENCES rcaowm (own_code)
>     , tnt_acno TEXT NOT NULL REFERENCES rcatnm (tnm_acno)
>     , tnt_date DATE NOT NULL
>     , tnt_value NUMERIC NOT NULL
> );
>
> What are the primary keys on the rcatnt and rcaowt tables?
>
> SELECT owm_name AS "Owner_Name"
>     , tnm_name AS "Tenant_Name"
>     , sum(owt_value) AS "Sum_Owner_Transaction_Value"
>     , sum(tnt_value) AS "Sum_Tenant_Transaction_Value"
> FROM rcaowm
> JOIN rcaowt ON (own_code = owt_code)
> JOIN rcatnt ON (own_code = tnt_code)
> JOIN (
>      SELECT tnm_acno, tnm_name
>      FROM rcatnm
>     ) AS tenants ON (tnm_acno = tnt_acno)
> GROUP BY own_name, tnm_name;
>
> I've made an assumption that the owner you want is the one specified
> in the rcatnt table rather than the rcatnm table. Can a tenant have a
> transaction with an owner other than the one specified in the rcatnm
> table? I ask because if not, there's no reason to include the tnt_code
> column in rcatnt as it can be derived through the rcatnm table.
>
> As an aside, I'd highly recommend changing your table and column names
> to something easier to read. For example:
>
> CREATE TABLE owners
> (
>     owner_code TEXT PRIMARY KEY
>     , owner_name TEXT NOT NULL UNIQUE
> );
>
> CREATE TABLE tenants
> (
>     tenant_account_number TEXT PRIMARY KEY
>     , tenant_name TEXT NOT NULL UNIQUE
>     , owner_code TEXT NOT NULL REFERENCES owners (owner_code)
> );
>
> CREATE TABLE owner_transactions
> (
>     owner_code TEXT REFERENCES owners (owner_code)
>     , owner_transaction_date DATE NOT NULL
>     , owner_transaction_value NUMERIC NOT NULL
> );
>
> CREATE TABLE tenant_transactions
> (
>     owner_code TEXT NOT NULL REFERENCES owners (owner_code)
>     , tenant_account_number TEXT NOT NULL REFERENCES tenants
> (tenant_account_number)
>     , tenant_transaction_date DATE NOT NULL
>     , tenant_transaction_value NUMERIC NOT NULL
> );
>
> This makes the query much less cryptic:
>
> SELECT owner_name AS "Owner_Name"
>     , tenant_name AS "Tenant_Name"
>     , sum(owner_transaction_value) AS "Sum_Owner_Transaction_Value"
>     , sum(tenant_transaction_value) AS "Sum_Tenant_Transaction_Value"
> FROM owners
> JOIN owner_transactions USING (owner_code)
> JOIN tenant_transactions USING (owner_code)
> JOIN (
>      SELECT tenant_account_number, tenant_name
>      FROM tenants
>     ) AS tenants USING (tenant_account_number)
> GROUP BY owner_name, tenant_name;
>
> You'll thank yourself in the future when you try to read your code :)
>
> Hope this helps.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
Hi Michael

Thanks for replying.

I tried your suggestion and ended up with the same problem I was
experiencing i.e. because there are multiple owner and tenant
transaction records the sum of the values are multiplying by the number
of transactions i.e.

Owner transactions:

AAA, 20070801, -3500.0
AAA, 20070802, -500.0
AAA,20070803,399.0

Tenant transactions:

AAA,BBB,20070801,3500.0
AAA,BBB,20070802,500.0

The result of the select statement is:

Owner Name                               -7202.0       (-3500-500+399) 2
times i.e. the number of tenant transactions
Tenant Name                             12000.0       (3500+500) 3 times
i.e. the number of owner transactions

Help!

Thanks, Paul