SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    m2m table with two db's

    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

  2. #2
    SitePoint Zealot falsealarm's Avatar
    Join Date
    Sep 2008
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,243
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you all.

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

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •