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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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