Member Avatar for pitic

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 I have also tried with join but it's not what i want (at least how i tried it) because there is no link between tables.

Thanks

  • 2 Contributors
  • 2 Replies
  • 408 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by pitic
Member Avatar for smantscheff

Precede p_id by the table and the database name:

where db1.t1.p_id=db2.t2.p_id and db1.t1.p_id=db3.t3.p_id
Edited by smantscheff because: n/a
Member Avatar for pitic

ey. 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.