Re: Querying multiple tables - Mailing list 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