Hi. I have 3 tables:
Table1: AccountInfo
AccountInfoID bigint <-- primary key
AccountNumber char
PostingDate datetime
TransactionReferenceNumber char
SequenceNumber numeric
Table2: LodgingSummary
LodgingSummaryID bigint
AccountInfoID bigint <-- foreign key
LoadTransactionCode tinyint
NoShowIndicator decimal
CheckInDate datetime
DailyRoomRate decimal
TotalOtherCharges decimal
Table3: Load_LodgingSummary
LodgingSummaryID bigint
LoadTransactionCode tinyint
AccountNumber varchar
PostingDate datetime
TransactionReferenceNumber varchar
SequenceNumber numeric
NoShowIndicator numeric
CheckInDate datetime
DailyRoomRate numeric
TotalOtherCharges numeric
I need to insert the AccountInfoID from AccountInfo along with
LodgingSummaryID, LoadTransactionCode, NoShowIndicator, CheckInDate,
DailyRoomRate, TotalOtherCharges from Load_LodgingSummary into the LodgingSummary table (which will be empty from the start).
I have devised the following query:
[CODE]
set identity_insert LodgingSummary on
insert into LodgingSummary
(
LodgingSummaryID,
AccountInfoID,
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges
)
select
LodgingSummaryID,
(select min(ai.AccountInfoID) from AccountInfo ai
where ai.AccountInfoID not exists (select AccountInfoID from LodgingSummary l
where l.AccountInfoID= ai.AccountInfoID)) as AccountInfoID,
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges,
TotalTaxAmount
from Load_LodgingSummary
set identity_insert lodgingsummary off
[/CODE]
When I run the query, I only get the first AccountInfoID from
AccountInfo. The data in LodgingSummary looks like (table shortened
for brevity):
LodgingSummaryID AccountInfoID LoadTransactionCode
1 1 4
2 1 4
3 1 4
4 1 4
etc...
I want LodgingSummary to look like:
LodgingSummaryID AccountInfoID LoadTransactionCode
1 1 4
2 2 4
3 3 4
4 4 4
etc...
How do I fix the subquery in the select statement (in red above) to get what I want? Thanks!