Bal

Hi chaps,

I have the following SQL query (SQL 2005).
Its basically retrieving some values using simple joins.
However there appears to be a problem with the LEFT OUTER JOIN:
"LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
"
When I try to compile the code i Get :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OrderLineItemTransaction.OrderLineItemTransaction ID" could not be bound.

Any help would be appreciated.

Cheers
Bal

SELECT
ord.orderDate,
cc.forename + ' ' + cc.surname person,
prod.description,
oli.noofitems,
deladdr.housenameno + ' ' + deladdr.addressLine1 + ' ' + deladdr.addressLine2 + ' ' + deladdr.city + ' ' + deladdr.postcode + ' ' + deladdr.county + ' ' + deladdr.country deladdress
FROM
product prod,
OrderLineItem oli,
[Order] ord,
OrderTransaction ordT,
OrderLineItemTransaction oliT,
CustomerContact cc,
Customer cust,
DDDispatchedOrder dd,
address deladdr,
address invaddr
LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
WHERE
prod.productID = oli.productID
AND ord.orderID = oli.orderID
AND ord.orderID = ordT.orderID
AND oliT.orderlineitemID = oli.orderlineitemID
AND cc.customercontactID = ord.customercontactID
AND cc.customerID = cust.customerID
AND ord.invoiceaddressID = invaddr.addressID
AND ord.deliveryaddressID = deladdr.addressID
AND ordT.dispatchTypeID = 2

DemonDave

iTrader: (13)

it looks like you have got a space or a funny char in the column indentifier

(OrderLineItemTransaction.OrderLineItemTransaction ID =

Hanley

Deffo looks like it's the space causing the problem

RichB

normally you can say select * from somelongtablename a left join someotherlongtablename2 b on a.id=b.id so in effect you are trying to create an 'alias' of OrderLineItemTransaction.OrderLineItemTransaction and name it ID....
So my vote is also that the spaces are doing it - Use '_' instead of space
Or something like that...

EP82

I'd say space too, hope you lay your syntax out better than that.

Makes it a hell of alot easier for figuring out why it wont work

AllenJ

I guess your question has been answered.

Remove the space between Transaction and ID.

jpor

iTrader: (1)

Can you not put " " at the start and end of the field you want to join? Or set that field up with an alias name in you're SELECT.

Dracoro

If SQLServer then you could use the following syntax: [OrderLineItemTransaction ID]