We need to know what kind of db server you're using. The answer will invariably be that you need, in MS SQL Server terminology, a linked server. However, my experience has been that this does not always work with CF, even though the query will work in SSMS, because the JDBC driver doesn't understand the 4-part syntax (servname.database.scheme.table.columname) and gives an error like "too many dots." In that case I have not found a workaround that makes it possible to do the JOIN inside of the query. The solution ends up being that you need to create the linked servers, and then create an SP on one of them (the one that the CF datasource points to) to do the actual query, and then call that SP from your CF code. That way it's SQL Server, and not JDBC, that handles the 4-part name.
i am using sql 12 and we do have link server but i never done this before so don't know how to call?
If both databases are sql server then you can probably use this type of syntax.
from table1 join LINKEDSERVERNAME.OWNER.TABLE on something.
If the linked server is something else then you will be better off with a stored procedure that uses openquery.