Problem joining two tables 1

[jgurgen]

i have two tables

TABLE A
LoanID
HistoryCounter
DateMailed
TransactionCode
TransactionAmt

TABLE B
LoanID
HistoryCOunter
TransactionDate
TransactionCode
TransactionAmt

All i want is to have a view where i can select all items in table a and b at the same time.

VIEW
LoanID
HistoryCounter
TransactionDate
TransactionCode
TransactionAmt

there are no identical items in each table but common loanids. if the transactioncode is above 500 then it will come from table a. How can i combine these two tables into 1 view?

 

[AlexCuse]

I think you want a union query. You have not been too clear about what you want (do you only want to show each LoanID once or what? That would complicate things)

This should get you started:

create view DaView as

select * from tableA where transactionCode > 500

union all select * from tableB where transactionCode <= 500

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jgurgen]

yeah i only want to show the loanid once

 

[jgurgen]

This seems to work but is this the most efficient way?

select LoanID, HistoryCounter, DateMailed as TransactionDate, TransactionCode, TransactionAmt
from ifs.dbo.isplit_disb_DisbTran dt where loanid='0000110806' and datemailed is not null
union all 
select LoanID, HistoryCounter, TransactionDate, TransactionCode, TransactionAmt from ifs.dbo.isplit_disb_paymenttran pt 
where loanid='0000110806'
order by historycounter

 

[AlexCuse]

Wow, that is a tough one. Before doing anything else, will the whole record be identical or just the loanID?

I have an idea how to do it, but I don't have time to get into it right now. Can you post some sample data and your expected results (along with what you have tried so far)? Another thing we'll need to know is how you decide which record to show if you have multiple ones for each loanID. I think this would help you to get an answer faster.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jgurgen]

the only thing identical is the loanid

here is some sample data copy and paste into notepad for better viewing

select LoanID, HistoryCounter, DateMailed as TransactionDate, TransactionCode, TransactionAmt
from ifs.dbo.isplit_disb_DisbTran dt where loanid='0000110806' and datemailed is not null



LoanID     HistoryCounter TransactionDate                                        TransactionCode TransactionAmt        
---------- -------------- ------------------------------------------------------ --------------- --------------------- 
0000110806 8              2006-10-25 00:00:00.000                                530             -4.9700
0000110806 14             2006-12-19 00:00:00.000                                730             -4.0500
0000110806 15             2006-12-20 00:00:00.000                                530             -4.9700
0000110806 17             2007-01-29 00:00:00.000                                722             -10.0000
0000110806 18             2007-01-29 00:00:00.000                                730             -55.0000
0000110806 36             2007-03-09 00:00:00.000                                530             -4.9700

select LoanID, HistoryCounter, TransactionDate, TransactionCode, TransactionAmt from ifs.dbo.isplit_disb_paymenttran pt 
where loanid='0000110806'


LoanID     HistoryCounter TransactionDate                                        TransactionCode TransactionAmt        
---------- -------------- ------------------------------------------------------ --------------- --------------------- 
0000110806 10             2006-11-06 00:00:00.000                                240             19.8800
0000110806 11             2006-11-06 00:00:00.000                                300             180.1200

(2 row(s) affected)

 

[AlexCuse]

Ok. How should one tell which row you want to show for each LoanID? Is it simply the most recent one?

Ignorance of certain subjects is a great part of wisdom

 

[jgurgen]

im not sure i understand the question but i want to get all rows from both tables and order it by transactiondate

 

[AlexCuse]

yeah i only want to show the loanid once

Did you mean only once per row? If you really want all rows from both tables it is simply this (assuming your TransactionDate is a date/time not an evil varchar column)

create view leView
as

select * from tableA

union all select * from tableB

order by [Transaction Date]

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[jgurgen]

thats what i had but wasnt sure that was the ideal way, thanks for the help appreciate it