September 24, 2009 at 12:55 pm
Hi!
I was wondering if table alias' are allowed on multiple table joins?
Somthing like this:
FROM CRM_OrganizationalUnits INNER JOIN
CRM_Orders ON CRM_OrganizationalUnits.ORGANIZATIONAL_ID = CRM_Orders.ORGANIZATIONAL_ID INNER JOIN
CRMv_Order_Transaction_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRMv_Order_Transaction_PartsLabor.TRANSACTION_ID INNER JOIN
CRM_StatusCodes ON CRMv_Order_Transaction_PartsLabor.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID as c1
Can I have the 'as c1'? I tried googling but can't seem to find an answer...
Thanks!!
-Michelle
September 24, 2009 at 1:07 pm
Well as a derived table you can do this
(SELECT * FROM table1 with inner join ... ) AS c1
for instance
Select t1, (Select t2 from table1 t where t.t1=t1) as t2 from table1
hope you are looking for something like this
September 24, 2009 at 1:08 pm
Hi,
You cannot directly do that. Here's what you can do, either change your query so its a subquery:
SELECT *
FROM (SELECT *
FROM CRM_OrganizationalUnits
INNER JOIN CRM_Orders
ON CRM_OrganizationalUnits.ORGANIZATIONAL_ID = CRM_Orders.ORGANIZATIONAL_ID
INNER JOIN CRMv_Order_Transaction_PartsLabor
ON CRM_Orders.TRANSACTION_ID = CRMv_Order_Transaction_PartsLabor.TRANSACTION_ID
INNER JOIN CRM_StatusCodes
ON CRMv_Order_Transaction_PartsLabor.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID) AS c1
Or, you can use an alias on every table (or only 1 of them if you want) so it simplifies the joining.
SELECT *
FROM CRM_OrganizationalUnits C1
INNER JOIN CRM_Orders c2
ON C1.ORGANIZATIONAL_ID = c2.ORGANIZATIONAL_ID
INNER JOIN CRMv_Order_Transaction_PartsLabor c3
ON c2.TRANSACTION_ID = c3.TRANSACTION_ID
INNER JOIN CRM_StatusCodes c4
ON c3.TRANSACTION_ID = c4.TRANSACTION_ID
I was not sure what you required, so I put both examples.
Hope that helps, feel free to ask questions if you're not sure.
Cheers,
J-F
September 24, 2009 at 1:12 pm
Aw, cool! Thanks guys... this is part of a much bigger more complicated problem I'm working on, so I might be back, but I hope not!
-Michelle
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy