How should i make a query extracting data from two connections.
I have 4 connections in SC. 1st is the conn_general with all member data, the other 3 connections (eg conn_project1, conn_project2 and conn_project3) containing data for the specific project. Now i want to create ‘join’ between two table with a different connection. So i can replace eg member #1 with the lastname/date of birth/… stored in a table in conn_general.
Is this possible?
It depends on the database it is possible with maria/mysql… and also on oracle (I know that for sure).
In oracle you need a database link which you then use in your query and in mysql you can do it with federated in the query. But exactly how it is done in mysql I dont know. Check out stackoverflow.com for those kinds of questions…
For mysql, maria there is plenty of info around about this, but generally you just use the database.tablename.fieldname format along with UNION
When i try this in SC SQMbuilder i get an error on following code when i put the connectionname before the table name.
SELECT
conn_mysql_db_pers.tblmembers.LoginName,
conn_mysql_db_pers.tblgraad.Graad
FROM
conn_mysql_db_pers.tblmembers,
conn_mysql_db_pers.tblgraad
But when i drop the connectionname it works. So it seems SC SQLbuilder does not recognize the connectionname at the beginning.
SELECT
tblmembers.LoginName,
tblgraad.Graad
FROM
tblmembers,
tblgraad
@GuiGuy:
Possible to post a part of an sql-string to see how the format is?
with mysql\mariadb, sql statements used by sc_ macros can ‘override’ the connection setting (the db in the connection) by using the ‘dbname.dbtable’ syntax
There is a neat tutorial here: http://mysqltutorial.org/basic-mysql…on-mysql-aspx/
As a footnote, I often find that when certain SQL syntax is not directly supported by the SC code generator, it is best to create a VIEW in the database and call it from SC