Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning SQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning SQL
This is the forum to discuss the Wrox book Beginning SQL by Paul Wilton, John Colby; ISBN: 9780764577321
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning SQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 16th, 2010, 03:47 PM
EIS EIS is offline
Registered User
 
Join Date: Aug 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Is This SQL Possible?

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

Last edited by EIS; August 16th, 2010 at 03:53 PM.
Reply With Quote
  #2 (permalink)  
Old August 16th, 2010, 04:41 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
Code:
SELECT 'Check' AS transactionType, TH.MasterHdrKey, CK.checkAmount AS amount
FROM Treas_Header AS TH INNER JOIN Treas_Check AS CK
ON Treas_Header.MasterHdrKey=Treas_Check.MasterHdrKey
UNION
SELECT 'Deposit', TH.MasterHdrKey, DP.depositAmount
FROM Treas_Header AS TH INNER JOIN Treas_Deposit AS DP
ON Treas_Header.MasterHdrKey=Treas_Deposit.MasterHdrKey
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.
Reply With Quote
  #3 (permalink)  
Old August 16th, 2010, 04:42 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old August 16th, 2010, 10:18 PM
EIS EIS is offline
Registered User
 
Join Date: Aug 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old August 17th, 2010, 02:55 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #6 (permalink)  
Old August 17th, 2010, 09:02 PM
EIS EIS is offline
Registered User
 
Join Date: Aug 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Earl
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
creating ssis packagte for sql server to sql serer Laxmikant_it ASP.NET 3.5 Professionals 0 November 25th, 2008 11:23 PM
Converting from MS SQL 2005 to Sql Epress edition saif44 SQL Language 0 February 16th, 2007 03:17 PM
Failed to copy objects from SQL server to SQL Serv monfu SQL Server 2000 4 December 4th, 2005 04:54 PM
Move SQL DB from one sql to another sql server Israr SQL Server 2000 3 January 24th, 2005 01:13 PM



All times are GMT -4. The time now is 05:05 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.