m2m table with two db's

[IBazz]

Erm, I have a mental blockage and ask for your help.

I have two db's, well, because I think they are genuinely two separate entites. However, I have a need for a many-to-many table where one column refers to db1 and the other column refers to db2.

does this make for an inefficient query? I have to join two db's and two tables don't I? how do I do that?

bazz

[falsealarm]

If you are asking about this for a web application/site, I would recommend that you check and see if you host allows linking databases. If you are on mySQL 5.x, you may even be able to use a trigger to replicate table 1 on database 1 to table x on database 2. This may help simplify your solution but as I do not know the context in which you need this, these are just simple guesses.

[r937]

the mysql server where the query executes has to know where the two databases are

not sure myself how you set that up but it is often done

SELECT ... FROM db1.tbl1 INNER JOIN db2.tbl2 ON ...

[longneck]

no special trickery is needed. and triggers to keep the tables updated should definitely be avoided; total over-kill.

as rudy mentioned, you can reference tables in different databases by pre-pending the name of the database. just make sure that whatever user you use to log in to the mysql server has the necessary rights to both databases.

and i strongly suggest you use table aliases since you can't use the database.table.column notation with mysql. so do this:

select p.name as product_name
     , m.name as manufacturer_name
  from db1.products p
  join db2.manufacturers m
    on m.id = p.manufacturer_id

[IBazz]

Thank you all.

I shall study that in the morning to store it better in my head.

bazz