>>>>>Hi,
>>>>>
>>>>>I have a situation where in the SQL Select, the JOIN should have a table (MyTable2) from another SQL Server database. Here is an example:
>>>>>

>>>>>local cJoinSqlServer
>>>>>cJoinSqlServer = "SQL2Name.dbo."
>>>>>cSqlSelect = "select * from mytable join " + cJoinSqlServer + "MyTable2 on MyTable.pk_field = MyTable2.pk_field"
>>>>>

>>>>>The above syntax works.
>>>>>And then I tried to add the cJoinSqlServer to the ON part as follows:
>>>>>

>>>>>local cJoinSqlServer
>>>>>cJoinSqlServer = "SQL2Name.dbo."
>>>>>cSqlSelect = "select * from mytable join " + cJoinSqlServer + "MyTable2 on MyTable.pk_field = " + cJoinSqlServer + "MyTable2.pk_field"
>>>>>

>>>>>
>>>>>The above works too; no error. But do I really need to add this prefix (of the second SQL Server DB) to the ON part?
>>>>
>>>>KISS and keep it clear IMHO, yours is not, whether you use the full dbname.schema.tablename or not:
>>>>
>>>>cJoinSqlDatabase = "SQL2Name.dbo."
>>>>

>>>>TEXT TO cSqlSelect TEXTMERGE NOSHOW PRETEXT 15
>>>>	Select t1.fieldName1, ..., t1.fieldNameN, t2.fieldName1, ..., t2.fieldNameN, t3.fieldName1, ..., t3.fieldNameN  
>>>>		FROM myTable t1
>>>>		Join << m.cJoinSqlDatabase >>.MyTable2 t2 on t1.pk_field = t2.pk_field
>>>>              join << m.cJoinSqlDatabase >>.MyTable3 t3 on t2.pk_field = t3.pk_field
>>>>ENDTEXT
>>>>

>>>>PS: Do not forget to rename fields which have the same name (ie: pk_field).
>>>
>>>I just looked into changing my SQL Select to use aliases and realized why I didn't use them before.
>>>My SQL Select has all selected fields with table name aliases. And I would have to change all these aliases with T1 or T2. And I am concerned that I will not change the alias in one place and the code will break.
>>
>>So are you saying you are instead going to take the longer path and alias them all with their databases as well? Like:
>>< m.cJoiningDatabase >..myTable3.myField??? I thought t3.myfield would be easier.
>
>I don't know what is easier. My main goals are 1) not to break anything that works 2) maintain stability.
>And I will think again about this case if I would want to change it as you suggest. I just want to take time to think and consider everything.

The most simple solution: Use aliasnames identical to table names. Although the Alias will look like the real table, it is an Alias and will be used as such in the SQL Query.

If you do the following:

Join << m.cJoinSqlDatabase >>.MyTable2

or

Join << m.cJoinSqlDatabase >>.MyTable2 MyTable2

Both will result in an alias "MyTable2" which is then used in the rest of the query. So even if MyTable2 would exist in any other database, it will not cause an error, which I believe was your initial concern.

If you create different alias names than your actual table names, you will have an issue with codesnippets and factories that only know the table names. The only situation where you really need to use a different alias name is when you select from the same table name twice in one query.