2

I am working in Moodle and CodeIgnitor together in one project; a few of my tables are in moodle db and other tables are in another database.

Is it possible to implement JOIN queries with two Mysql Databases to fetch the data we require?

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
OM The Eternity
  • 15,694
  • 44
  • 120
  • 182
  • 1
    possible duplicate of [MySQL -- join between tables in 2 different databases?](http://stackoverflow.com/questions/5698378/mysql-join-between-tables-in-2-different-databases) – Lightness Races in Orbit Aug 09 '11 at 10:46

3 Answers3

2

YES

  • both database are using the same daemon (meaning same port, same server)
  • user account has enough ACL to access both databases
  • if you are able to construct query yourself

NO

  • certain framework / data modelling will restrict you do so (that's mean the CODE teaching you how to do the SQL, and you cannot against their rules (unless putting your hack)
ajreal
  • 46,720
  • 11
  • 89
  • 119
1

Yes.

If DB are on the same server, you can use 

select a.col from db1.table1 a, db2.table2 b where a.col = b.col

Obviously you would put your join condition in here.

diagonalbatman
  • 17,340
  • 3
  • 31
  • 31
0

Yes.

Instead of using the "default" (currently selected) database, you can explicitly specify the database name when you reference your tables and fields:

You can refer to a table within the default database as tbl_name, or as db_name.tbl_name to specify a database explicitly. You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name

However, I don't recommend this. If data are related between tables then they are supposed to be in the same database. That's what would make sense!

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
  • I don't agree with your last statement. It is perfectly reasonable to have two systems which are discreet in their operation, however for example for Management Information purposes simmilar data needs to be assimilated for reporting, for example financial transactions. It wouldn't be appropriate for both systems to use the same DB. – diagonalbatman Aug 09 '11 at 11:46
  • Tables represent data relationships. Databases are groupings of these relations, each of which can be cross-examined to produce intersections and other nice stuff. The database is the singular, all-encompassing body of potentially-related data. One system, one database. I _can_ see, however, that it might be useful when accumulating data from multiple systems. – Lightness Races in Orbit Aug 09 '11 at 11:49
  • I wasn't disputing the definition of a DB. – diagonalbatman Aug 09 '11 at 11:51
  • Please remove your downvote. We don't downvote for disagreeing with opinions here, like we do on Meta. We downvote for "significantly wrong or irrelevant or misleading or offers really bad advice" and -- given my factual answer and quote from the documentation -- I can't see how that applies here. – Lightness Races in Orbit Aug 09 '11 at 12:01
  • @TomalakGeret'kal let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2282/discussion-between-diagonalbatman-and-tomalak-geretkal) – diagonalbatman Aug 09 '11 at 15:46