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

Re: Querying multiple tables

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Paul Malherbe <paul(at)tartan(dot)co(dot)za>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Querying multiple tables
Date: 2007-08-01 15:32:23
Message-ID: BD47F02A-C8D5-4978-A18B-BDCF45250878@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-novice
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




In response to

Responses

pgsql-novice by date

Next:From: Paul MalherbeDate: 2007-08-01 16:36:06
Subject: Re: Querying multiple tables
Previous:From: Paul MalherbeDate: 2007-08-01 12:33:56
Subject: Querying multiple tables

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