how to select fields from different db's with the same table and field name

Question

I have two databases, for argument sake lets call them db1 and db2. they are both structured exactly the same and both have a table called table1 which both have fields id and value1.

My question is how do I do a query that selects the field value1 for both tables linked by the same id???

Answer 1

Try something such as this.

$dbhost="server_name";
$dbuser1="user1";

$dbpass1="password1";

$dbname1="database_I";
$dbname2="database_II";
$db1=mssql_connect($dbhost,$dbuser1,$dbpass1);

mssql_select_db($dbname1,$db1);

$query="SELECT ... FROM database_I.table1, database_II.table2 WHERE ....";

etc. Sorry if this does not help.

Answer 2

There is an easy way in sql. Extend your syntax for FROM clause, so instead of using select ... from tablename, use

select ... from database.namespace.tablename

The default namespace is dbo.

Answer 3

You could use a union select:

Simple example:

select "one" union select "two";

This will return 2 rows, the first row contains one and the 2nd row contains two. It is as if you are concatenating 2 sql quires, the only constant is that they both must return the same number of columns.

Multiple databases:

select * from client_db.users where id=1 union select * from master_db.users where id=1;

In this case both users databases must have the same number of columns. You said they have the same structure, so you shouldn't have a problem.

Answer 4

This is T-Sql, but I can't imagine mysql would be that much different (will delete answer if that's not the case)

SELECT
  a.Value1 AS [aValue]
  ,b.Value1 AS [bValue]
FROM
  db1.dbo.Table1 a
  INNER JOIN db2.dbo.Table1 b
    ON a.Id = b.Id

Answer 5

You can prefix the table names with the database name to identify the two similarly named tables. You can then use that fully qualified table name to refer to the similarly named fields.

So, without aliases:

select db1.table1.id, db1.table1.value1, db2.table1.value1
from db1.table1 inner join db2.table1 on db1.table1.id = db2.table1.id

and with aliases

select t1.id, t1.value1, t2.value1
from db1.table1 as t1 inner join db2.table1 as t2 on t1.id = t2.id

You may also want to alias the selected columns so your select line becomes:

select t1.id as id, t1.value1 as value_from_db1, t2.value1 as value_from_db2