Skip site navigation (1) Skip section navigation (2)

Re: Querying multiple tables

From: Paul Malherbe <paul(at)tartan(dot)co(dot)za>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Querying multiple tables
Date: 2007-08-01 16:36:06
Message-ID: 46B0B676.2030104@tartan.co.za (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-08-01 17:58:13
Subject: Re: Querying multiple tables
Previous:From: Michael GlaesemannDate: 2007-08-01 15:32:23
Subject: Re: Querying multiple tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group