Newbie SQL Question

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
bhummelAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
richtsteigConnect With a Mentor Commented:
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
 
alokmCommented:
I would appreciate detailed table structure.
0
 
MikeP090797Commented:
If it has the same structure, use INNER JOIN
0
 
a111a111a111Commented:
bhummel,
Hi,

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

shay@hili.com

0
 
bhummelAuthor Commented:
richtstieg,
I used your code and it combined the rows. Thanks for your help.

Brad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.