April 30, 2012 at 5:10 am
I've had a browse round but I've not found anything that helps me, thus I'm here.
The multi-part identifier "BFlights.TransactionID" could not be bound.
I'm getting the above error on the below query/subquery and I have no idea why, I would normally join on the fields shown under normal circumstances.
SELECTRef, MAX(TransactionDate) AS [Transaction Date]
FROM dbo.Transactions
JOIN
(SELECT * FROM BFlights) AS Flights
ON BFlights.TransactionID = Transactions.TransactionID
WHERE (Ref = '129577')
GROUP BY Ref
Any ideas?
TIA
April 30, 2012 at 5:12 am
Qutip (4/30/2012)
I've had a browse round but I've not found anything that helps me, thus I'm here.The multi-part identifier "BFlights.TransactionID" could not be bound.
I'm getting the above error on the below query/subquery and I have no idea why, I would normally join on the fields shown under normal circumstances.
SELECTRef, MAX(TransactionDate) AS [Transaction Date]
FROM dbo.Transactions
JOIN
(SELECT * FROM BFlights) AS Flights
ON BFlights.TransactionID = Transactions.TransactionID
WHERE (Ref = '129577')
GROUP BY Ref
Any ideas?
TIA
The error is on the join clause, you have decalred the BFlights select in the join as Flights then tried to join on BFlights instead of Flights
SELECTRef, MAX(TransactionDate) AS [Transaction Date]
FROM dbo.Transactions
JOIN
(SELECT * FROM BFlights) AS Flights
ON Flights.TransactionID = Transactions.TransactionID
WHERE (Ref = '129577')
GROUP BY Ref
April 30, 2012 at 6:03 am
That's my mistake when removing the dbo's before I copied the SQL. I still get that error.
April 30, 2012 at 6:06 am
can you please post the create table statements for each table, will hopefully help us understand a bit more to whats going on
also are BFlights and Transactions in the same DB?
April 30, 2012 at 6:33 am
anthony.green (4/30/2012)
can you please post the create table statements for each table, will hopefully help us understand a bit more to whats going onalso are BFlights and Transactions in the same DB?
As requested, thanks! And yes they're both in the same DB
CREATE TABLE Transactions
(
TransactionID int,
Transactiondate datetime,
Ref varchar(20),
BrandID smallint,
WhoreserveID smallint,
FinanceID int,
OriginalInvoiceDate datetime,
CreatedDate datetime,
Pax int,
IsPackage bit,
Isfilepackage bit,
IsChannelPhone bit,
WhoReserveCode varchar(5)
)
CREATE TABLE BFlights
(
BFlightID int,
TransactionID int,
DepDate datetime,
ArrDate datetime,
CreatedDate datetime,
DepAPID smallint,
ArrAPID smallint
)
April 30, 2012 at 6:38 am
where does the column TransactionDate come from, its not in any of the two tables?
is there more at play here than the snippit you provided?
April 30, 2012 at 6:47 am
Updated, I forgot transaction date :doze: and I've added another couple of fields that I didn't think had any consequence.
April 30, 2012 at 6:51 am
way 1
SELECT
Ref
,MAX(TransactionDate) AS [Transaction Date]
FROM
Transactions
JOIN
BFlights
ON
BFlights.TransactionID = Transactions.TransactionID
WHERE
(Ref = '129577')
GROUP BY
Ref
way2
SELECT
Ref
,MAX(TransactionDate) AS [Transaction Date]
FROM
Transactions
JOIN
(SELECT * FROM BFlights) AS BFlights
ON
BFlights.TransactionID = Transactions.TransactionID
WHERE
(Ref = '129577')
GROUP BY
Ref
way 3
SELECT
Ref
,MAX(TransactionDate) AS [Transaction Date]
FROM
Transactions
JOIN
(SELECT * FROM BFlights) AS Flights
ON
Flights.TransactionID = Transactions.TransactionID
WHERE
(Ref = '129577')
GROUP BY
Ref
April 30, 2012 at 6:56 am
Thanks, an obvious error that I couldn't see.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply