I am writing a database application using the Borland Database Engine (BDE) in C++ BUilder 2010. The database application is used by the church treasurer to record financial transactions. The application links to a Paradox database with several tables. The main table consists of the transaction date, the transaction type (check or deposit), and a primary key to link to check and deposit tables. The check and deposit tables link to check detail and deposit detail tables where amounts and budget codes are assigned. For example, a single check of $100 might consist of $25 for office supplies and $75 for new equipment. Similarly a $1000 deposit might consist of $800 undesignated and $200 for a special offering.

Note: When the transaction is a check, there are no deposit records and similarly when the transaction is a deposit, there are no check records.

For example, I can retrieve the transactions for checks with budget code 101.3 with the following SQL statement:

"SELECT * FROM Treas_Header INNER JOIN Treas_Check
ON Treas_Header.MasterHdrKey=Treas_Check.MasterHdrKey
INNER JOIN Treas_CheckDetail
ON Treas_Check.CheckKey=Treas_CheckDetail.CheckKey
WHERE Treas_CheckDetail.BudgetCode='101.3';

Similarly, I can retrieve the transactions for deposits with budget code 101.3 with the following SQL statement:

SELECT *
FROM Treas_Header INNER JOIN Treas_Deposit
ON Treas_Header.MasterHdrKey=Treas_Deposit.MasterHdrK ey
INNER JOIN Treas_DepositDetail
ON Treas_Deposit.DepositKey=Treas_DepositDetail.Maste rDepositKey
WHERE Treas_DepositDetail.BudgetCode='101.3';

My problem is I want both of the results returned together. How can I modify the SQL statements to return both sets of results?


The 'UNION' did not work because the Check and Deposit tables are different. Here is my latest SQL attempt:

SELECT DISTINCT d.MasterHdrKey, d.cleared, d.Recipient, d.Trans_Date,
d.Type, d.BankBalance, d.Amount,
D1.CheckKey, D1.MasterHdrKey, D1.Check_No, D2.CheckKey,
D1.IssuedTo,
D2.Amount, D2.Purpose, D2.BudgetCode, D2.BudgetDesc,
D3.DepositKey, D3.MasterHdrKey, D4.Amount,
D4.BudgetCode, D4.BudgetDesc, D4.Comments, D4.MasterDepositKey
FROM Treas_Header.db d, Treas_Check.DB D1, Treas_CheckDetail.DB D2,
Treas_Deposit.DB D3, Treas_DepositDetail.DB D4
WHERE (((D1.MasterHdrKey IS NOT NULL)
AND ((D1.MasterHdrKey = d.MasterHdrKey)
AND (D2.CheckKey = D1.CheckKey)
AND (UPPER(D2.BudgetDesc) like '%101.3%'
OR D2.BudgetCode LIKE '101.3')))
OR ((D3.MasterHdrKey IS NOT NULL)
AND ((D3.MasterHdrKey = d.MasterHdrKey)
AND (D4.MasterDepositKey = D3.DepositKey)
AND (UPPER(D4.BudgetDesc) like '%101.3%'
OR D4.BudgetCode LIKE '101.3'))))

The above SQL appears to be returning a zillion records because I have to abort the program because the SQL never returns.

Is it possible to get the records with a common BudgetCode from two different non-similar tables with a single SQL statement?

Thank You...
Earl

You do need to use a UNION, but you need to call out the fields from the two tables *INDIVIDUALLY* and then you have to *MATCH UP* the fields carefully.

Just for example, you might do Notice that when you do a UNION, *ONLY* the field names from the FIRST SELECT in the UNION are "visible" in the results. So 'Deposit' will automatically get the name "transactionType" and DP.depositAmount will get the name "Amount".

The number of fields *AND* the field TYPES (text, number, etc.) from the two SELECTs must match! (The field types have *some* lenience. For example, you could get an integer from one table and a MONEY amount from the other, most likely. Depends on what Paradox considers "compatible" types.) But the names of the fields have NO significance at all.

This is really the only practical way to write the query you are after.

Oh, one thing: If you need a field from one of the SELECTs that simply has no corresponding value, at all, from the other one, then just supply a NULL (or any constant value that makes sense to you) in its place. If the field that is "missing" is in the first SELECT, then still supply the NULL or constant value and still use a "AS xxxx" to give it the name you need to see in the final UNION result.

FWIW, you should NEVER use SELECT * in any case except the very simplest. And *certainly* never if you are doing a JOIN. You are just inviting later troubles. Try to always use an explicit list of field names.

Thank you for your reply. I don't know if you are familiar with the Borland C++ Builder BDE but the returned records are loaded into DBGrid objects, one for each table in the database, and the program will complain if all columns in the DBGrid are not filled.

The tables are all linked in the program through the table's MasterFields and MasterSource property. Additionally, the tables were designed with referential integrity.

So why can't I just retrieve the records from the top transaction table and all the sub tables (check/check detail tables and deposit/deposit detail tables) will follow and fill in the appropriate records due to the MasterFields links?

I am a novice at this, so thank you for your patience. I am using the very good 'Beginning SQL' book as a reference.

Earl

Never used BuilderBDE but have used similar things, including early versions of Visual Studio with ASP.NET. And, yes, such products often think they know more than you do about your data.

In the case of VS/ASP.NET, in the early days, the answer was to create a Stored Procedure. Then VS would look at the fields in the SP, instead of the fields in the DB table, and create grids, et al., from those fields. (Current VS/ASP.NET is smarter...it allows you to specify a query as the grid source and figures out the fields by executing the query.) *IF* you could use a Stored Proc, maybe BDE would be able to do this???

Best advice I can offer.

Oh... Might not even need a Stored Proc for this, come to think of it. Just a VIEW would probably do the same thing.

Thank you for your reply. I have been thinking that I might need something other then a DBGrid to display the results.

Earl