table alias on multiple joins

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Rey-813636 SSC Enthusiast Points: 163 More actions · Answer 1

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

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

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

mm-1009269 SSCommitted Points: 1559 More actions · Answer 3

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