How about:
SELECT +
t1.PurchaseNumber, +
t3.AccountNo, +
t2.Amount +
FROM +
TransMaster t1, +
TransAcctID t2, +
Accounts t3 +
WHERE +
t1.TranID = t2.TranID +
AND +
t2.AcctID = t3.AcctID
And if you want to aggregate the Amount column:
SELECT DISTINCT +
t1.PurchaseNumber, +
t3.AccountNo, +
SUM(t2.Amount) +
FROM +
TransMaster t1, +
TransAcctID t2, +
Accounts t3 +
WHERE +
t1.TranID = t2.TranID +
AND +
t2.AcctID = t3.AcctID +
GROUP BY +
t1.PurchaseNumber, +
t3.AccountNo
These selects requires that your linking IDs are not null.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Atrix Wolfe
Sent: Wednesday, December 04, 2002 7:27 PM
To: [EMAIL PROTECTED]
Subject: select statement stumper
hello, im trying to make a select statement and i think in theory its
possible but cant get it to work.
heres what i have...
i have a tranmaster table with tranid's and PurchaseNumber's
i have a tranacctid table that has tranid's (tranmaster -> tranacctid is 1
to many), acctid's and amount
i have an accounts table that have accountno's and acctid's
heres what im trying to do:
im trying to show all transactions from tranmaster, showing all entries in
tranacctid for each tranid, but instead of showing acctid's, i need to link
to accounts to get the account numbers.
kinda complicated huh?
heres what i have so far, this shows all tranmaster/tranacctid info but
shows acctid's and not accountno's
sel PurchaseNumber, acctid, amount
from tranmaster left join tranacctid on tranmaster.tranid=tranacctid.tranid
now how would i take the above and make it link or join with the accounts
table to show the accountno instead of the acctid? Im pretty sure i could
do this with views, but is there any way with just a single select without
views?
Thanx for the help (:
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
