SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,207
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    joining 2 databases

    Code:
    myTable in db1
    
    (id) db1city
    (1) Rome 
    (2) London 
    (5) Paris 
    (7) Berlin 
    
    myTable in db2
    
    (id) db2city
    (1) Seoul 
    (4) Tokyo 
    (6) Peking
    I have two databases and each database has myTable like the above.


    I made the following would-be code for getting my target result below.

    Code:
    would-be code
    
    <cfquery datasource='db1,db2' name='db2'>
    select id,city
    from db1myTable,db2mytable
    order by id
    </cfquery>
    
    target result
    
    (1) Rome 
    (1) Seoul 
    (2) London 
    (4) Tokyo
    (5) Paris 
    (6) Peking
    (7) Berlin
    The would-be code above doesn't work correctly.
    How can I get my target result?

  2. #2
    SitePoint Evangelist
    Join Date
    Mar 2007
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It will most likely involve a UNION or UNION ALL. But the rest depends on your database. Which one are you using?

    Some database types allow access to multiple databases by using qualified object names: Caveat: The datasource users must have the appropriate permissions

    SELECT t1.Col1, ...
    FROM databaseOne.schemaName.TableOne
    UNION
    SELECT t1.Col1, ...
    FROM databaseTwo.schemaName.TableTwo

    Failing that, you could query the two tables separately and join the results using a QoQ with a UNION.

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,207
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cfStarlight View Post
    Which one are you using?
    I am using mySQL5.

    Code:
    SELECT t1.Col1, ...
    FROM databaseOne.schemaName.TableOne
    UNION
    SELECT t1.Col1, ...
    FROM databaseTwo.schemaName.TableTwo
    How can I desinate datasource?

    Code:
    <cfquery datasource='db1,db2' name='db12'>

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2007
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if mySQL supports that syntax, but if it does you could probably use either datasource. Assuming the datasource users have the appropriate permissions. (ie The user account must have permissions for the objects in both databases)

    Failing that, you could resort to using two separate queries. Then join them together using a QoQ and UNION.

  5. #5
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you mean you have those tables in two separate MySQL instances? Or are they just in separate databases on the same MySQL instance?

    It sounds to me like the latter, in which case Star's syntax should work fine. You should be able to use UNIONs, old-style joins (as below), JOIN ONs, etc. etc. without any problems.
    Code:
    SELECT   tbl1.id, tbl1.city, tbl2.id, tbl2.city
    FROM     db1.tbl1, db2.tbl2 ...
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  6. #6
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could also accomplish this in a view so then you can call it anytime you need it

    it is fair to say that this can be accomplished many different ways


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
  •