hi,
I have a number of different identical databases storing info about diferent regions of the world. I want to select data from lets say two databases and display it into one table/query. Both tables have the same fields. Any idea how I can do this?
i have tried the following:
select c1 from d1.t1 UNION select c1 from d2.t2 where c1='something'
the above does work, but it creates a union of the tables, rather than just getting the data and putting it into one temp table. The union would create a problem if lets say two fields were very similar (i think).
your help is much appreciated
selecting from two or more databases
Moderator: General Moderators
-
123newbiephp
- Forum Newbie
- Posts: 4
- Joined: Mon Nov 07, 2005 8:16 am
-
123newbiephp
- Forum Newbie
- Posts: 4
- Joined: Mon Nov 07, 2005 8:16 am
basically, i have 3 databases each with the same table. I want to select all the data in the 3 tables and output it to the browser. Am having issues with the mysql query to do this.
is this the right way to do it:
select * from database1.table1 where id ='$id' UNION select * from database2.table2 where id ='$id' UNION select * from database3.table3 where id ='$id'
the above does work, but is there an easier way of doing this? soon i'll be wanting to select from many more databases and the query will get very large.
is this the right way to do it:
select * from database1.table1 where id ='$id' UNION select * from database2.table2 where id ='$id' UNION select * from database3.table3 where id ='$id'
the above does work, but is there an easier way of doing this? soon i'll be wanting to select from many more databases and the query will get very large.
-
123newbiephp
- Forum Newbie
- Posts: 4
- Joined: Mon Nov 07, 2005 8:16 am
What dbms are you using? because I seriously think you should look into database theory, design and practice. Structuring the database well with well placed indexs and there is no reason at all 300k records should cause the system to slow down (believe me I know, I spend a LOT of my time working on large datasets).
Rather than setting up separate databases with one table each in them, set up one database that has all the tables in it. Multi-table selects within one DB are simple.
But like others have said, if the content is basically the same between all the tables, you should be able to do even more combination of the fields and make it efficient. Lots of rows should not be a performance issue if you are primarily reading from them and you have proper indexes set up.
But like others have said, if the content is basically the same between all the tables, you should be able to do even more combination of the fields and make it efficient. Lots of rows should not be a performance issue if you are primarily reading from them and you have proper indexes set up.