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,
> 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"
JOIN rcaowt ON (own_code = owt_code)
JOIN rcatnt ON (own_code = tnt_code)
SELECT tnm_acno, tnm_name
) 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_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"
JOIN owner_transactions USING (owner_code)
JOIN tenant_transactions USING (owner_code)
SELECT tenant_account_number, tenant_name
) 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.
grzm seespotcode net
In response to
pgsql-novice by date
|Next:||From: Paul Malherbe||Date: 2007-08-01 16:36:06|
|Subject: Re: Querying multiple tables|
|Previous:||From: Paul Malherbe||Date: 2007-08-01 12:33:56|
|Subject: Querying multiple tables|