mysql select from multiple tables / databases

[piticcotoc]

Hy. I'm trying to do a select from multiple databases and tables for an occurence. let's say i have databases db1, db2 and db3. Each have the following tables t1, t2, t3. And in each table there is a field named id which i want to select and a field and p_id which i want to use as a condition. To give a better idea this is an example of what results i want to get:

---------------------------------------
|id_t1 | id_t2 | id_t3 | database_name|

| 100 | 123 | 356 | db1 |
| 252 | 156 | 566 | db2 |
|... | ... | ... | db3 |

How can i pass the p_id condition to each table and each database. I tried this: select t1.id as id_t1, t2.id as id_t2, t3.id as id_t3, (select database()) as database_name from db1.t1, db1.t2, db1.t3 where ****can't make the condition work because if i only say p_id = 100 it says it's ambiguous**** union select *** all the above *** from db2 and db3 Thanks

[Mycroft Holmes]

A couple of relevant assumptions, you are using SQL Server and all the databases are on the same server. If you are getting the database name from a variable then you need to use dynamic sql.

Select idt1,idt2,idt3
from db1.dbo.tablename

Select idt1,idt2,idt3
from db2.dbo.tablename

Select idt1,idt2,idt3
from db3.dbo.tablename

Now insert UNION between the 3 queries to get them in 1 result set

Never underestimate the power of human stupidity RAH

[piticcotoc]

Hey. Thanks for the reply, but what you suggested does not apply for me. I'm trying to select from 3 different tables from each of the 3 databases the rows which contain a certain value. In the mean time i have completed the query selecting the rows from one table, union them with the other tables in one database, then union the whole select with the other two databases. Here is what i did

SELECT * FROM (SELECT id, "t1", "db1" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db1" FROM db1.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db1" FROM db1.t3 WHERE p_id = "") s1
UNION
SELECT * FROM (SELECT id, "t1", "db2" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db2" FROM db2.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db2" FROM db2.t3 WHERE p_id = "") s2
UNION
SELECT * FROM (SELECT id, "t1", "db3" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db3" FROM db3.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db3" FROM db3.t3 WHERE p_id = "") s3

If you have a better option for this I'm looking forward to see it, because this gets really crowded when i have more tables and databases. And yes, I am using MySql and all the databases are on the same server. The db-s are the same with db1 being main and the other are backups which differ only in the content not in fields and tables

[jschell]

To be clear - your database is MySQL. To use different databases in the same SQL requires that the SQL that the database uses provides a way to specify the database itself as part of the name for the table. Far as I can tell MySQL does not allow that. The following is the documentation for what a 'table' can be. http://dev.mysql.com/doc/refman/5.0/en/join.html[^] This doesn't totally eliminate the possibility since this might just not specify it. Finding the similar information for MS SQL Server is difficult. If it doesn't allow it then you must query each database independently and then merge them together yourself.

[piticcotoc]

of course you can use the database.table.field syntax on mysql. the query i wrote works i only asked if someone has a better approach because the query gets difficult to follow once i use more databases and tables. The join operation joins tables on certain values. I don't need the joined. I just need to see where in those tables (the row id) a value is found.