BobIa
asked on
Translate query with alias table names from Access sql to T-sql
This one is driving me nuts... I know my way around Access pretty well and can manually translate simple queries to sql. However, in this one I have to include the same table twice (AS dbo_Job and again as dbo_Job1) and join them to other tables. I'm really struggling with this. Would anyone care to guide me in the right direction. Functional Access query is attached. Thanks!
SELECT dbo_OrderCallOffSchedule.DatePlanned, dbo_Customer.CustomerCode, dbo_Job_1.JobReference, dbo_Customer.Name, dbo_Job_1.Address1, dbo_OrderHeader.OrderNumber, dbo_CustomerAddress.AddressCode, dbo_OrderLine.ManualDescription, dbo_CustomerAddress.Address1
FROM (((((dbo_OrderCallOffSchedule INNER JOIN dbo_OrderHeader ON dbo_OrderCallOffSchedule.OrderID = dbo_OrderHeader.OrderID) INNER JOIN dbo_OrderLine ON dbo_OrderCallOffSchedule.OrderLineID = dbo_OrderLine.OrderLineID) LEFT JOIN dbo_CustomerAddress ON dbo_OrderHeader.DeliveryAddressID = dbo_CustomerAddress.CustomerAddressID) LEFT JOIN dbo_Job ON dbo_CustomerAddress.CustomerAddressID = dbo_Job.CustomerAddressID) INNER JOIN dbo_Customer ON dbo_OrderHeader.CustomerID = dbo_Customer.CustomerID) LEFT JOIN dbo_Job AS dbo_Job_1 ON dbo_CustomerAddress.JobID = dbo_Job_1.JobID
WHERE (((dbo_OrderCallOffSchedule.DatePlanned)=#9/27/2016#))
ORDER BY dbo_Customer.Name, dbo_Job_1.Address1, dbo_CustomerAddress.AddressCode;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Awesome. I provided you with my personal preferred way to format the SQL.
If all is well, please accept my solution as the answer.
If all is well, please accept my solution as the answer.
ASKER
Thanks Daniel
ASKER
Thanks! You're a life saver... was down to last brain cell on this one! I had to strip out and replace some underscores with periods but it works beautifully. It also helps me to understand the relationships and alias procedure for sql. Thanks again. I am posting the final version here for those who come after looking for a solution like this.
Open in new window