Solved

Newbie SQL Question

Posted on 1998-08-21
5
181 Views
Last Modified: 2011-09-20
Greetings,
I have created an SP which summarizes finacial statement information. I have created three temp tables(one with Revenue, one with Expenses, one with Excluded items).

I now want to: Merge these three temp tables into one so I can have one record with expenses, revenue and excluded items. The keys which are common for all temp tables are pool number, loan number and statement date.

How do I join these three tables.

Thank you for your help...
Brad
0
Comment
Question by:bhummel
5 Comments
 
LVL 2

Expert Comment

by:alokm
ID: 1430359
I would appreciate detailed table structure.
0
 
LVL 8

Expert Comment

by:MikeP090797
ID: 1430360
If it has the same structure, use INNER JOIN
0
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1430361
bhummel,
Hi,

E-mail me the database even with 2 records and I'll send you the code.

shay@hili.com

0
 
LVL 2

Accepted Solution

by:
richtsteig earned 100 total points
ID: 1430362
Hey bhummel,

I try to give you an answer:

First you have to decide, whether you want to create your result table (A) or have the structure already defined (B)

(A) You have to use the SELECT INTO command
(B) Your have to use the INSERT INTO command

If your three source tables are ALL containing the keydata, you can use the INNER JOIN, if one table has no data, you should use the LEFT JOIN

Example

SELECT REV.<fld1>,.. REV.<fldn>,EXP.<fld1>,..EXP.<fldn>,XCL.<fld1>,.. XCL.<fldn>
    INTO <your new table>
   FROM (
             REVENUE AS REV
  INNER JOIN EXPENSES AS EXP
       ON REV.POOL = EXP.POOL AND REV.LOAN = EXP.LOAN
             )
 INNER JOIN EXCLUDE AS XCL
       ON REV.POOL = XCL.POOL AND REV.LOAN = XCL.LOAN
WHERE <your criteria>

Replace the "INNER" with "LEFT" if data in XCL or EXP is missing.

If your new structure is already defined the Statement would be:
INSERT INTO <your table>
SELECT REV.<fld1>,.. REV.<fldn>,EXP.<fld1>,..EXP.<fldn>,XCL.<fld1>,.. XCL.<fldn>
   FROM (
             REVENUE AS REV
  INNER JOIN EXPENSES AS EXP
       ON REV.POOL = EXP.POOL AND REV.LOAN = EXP.LOAN
             )
 INNER JOIN EXCLUDE AS XCL
       ON REV.POOL = XCL.POOL AND REV.LOAN = XCL.LOAN
WHERE <your criteria>

If you don't want to transfer all of the fields, you have to name
the destination fileds after the INSERT INTO
INSERT INTO <your table> (fldA, fldB, ....)

Hope this helps
0
 

Author Comment

by:bhummel
ID: 1430363
richtstieg,
I used your code and it combined the rows. Thanks for your help.

Brad
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question