Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

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;

Open in new window

0
BobIa
Asked:
BobIa
  • 2
  • 2
1 Solution
 
Daniel Van Der WerkenIndependent ConsultantCommented:
Try this. I tried to be careful, but I may have missed something.
SELECT 
		OCOS.DatePlanned, 
		CUST.CustomerCode, 
		JOB1.JobReference, 
		CUST.Name, 
		JOB1.Address1, 
		OH.OrderNumber, 
		CA.AddressCode, 
		OL.ManualDescription, 
		CA.Address1
FROM dbo_OrderCallOffSchedule OCOS
INNER JOIN dbo_OrderHeader OH
	ON OCOS.OrderID = OH.OrderID
INNER JOIN dbo_OrderLine OL
	ON OCOS.OrderLineID = OL.OrderLineID
LEFT JOIN dbo_CustomerAddress CA
	ON OH.DeliveryAddressID = CA.CustomerAddressID
LEFT JOIN dbo_Job JOB
	ON CA.CustomerAddressID = JOB.CustomerAddressID
INNER JOIN dbo_Customer CUST
	ON OH.CustomerID = CUST.CustomerID
LEFT JOIN dbo_Job JOB1 
	ON CA.JobID = JOB1.JobID
WHERE OCOS.DatePlanned = '9/27/2016'
ORDER BY CUST.Name, JOB1.Address1, CA.AddressCode;

Open in new window

0
 
BobIaAuthor Commented:
Daniel,
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.

SELECT 
		OCOS.DatePlanned, 
		CUST.CustomerCode, 
		JOB1.JobReference, 
		CUST.Name, 
		JOB1.Address1, 
		OH.OrderNumber, 
		CA.AddressCode, 
		OL.ManualDescription, 
		CA.Address1
FROM dbo.OrderCallOffSchedule OCOS
INNER JOIN dbo.OrderHeader OH
	ON OCOS.OrderID = OH.OrderID
INNER JOIN dbo.OrderLine OL
	ON OCOS.OrderLineID = OL.OrderLineID
LEFT JOIN dbo.CustomerAddress CA
	ON OH.DeliveryAddressID = CA.CustomerAddressID
LEFT JOIN dbo.Job JOB
	ON CA.CustomerAddressID = JOB.CustomerAddressID
INNER JOIN dbo.Customer CUST
	ON OH.CustomerID = CUST.CustomerID
LEFT JOIN dbo.Job JOB1 
	ON CA.JobID = JOB1.JobID
WHERE OCOS.DatePlanned = '9/27/2016'
ORDER BY CUST.Name, JOB1.Address1, CA.AddressCode

Open in new window

0
 
Daniel Van Der WerkenIndependent ConsultantCommented:
Awesome. I provided you with my personal preferred way to format the SQL.

If all is well, please accept my solution as the answer.
0
 
BobIaAuthor Commented:
Thanks Daniel
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now