CODEWITHSUNDEEP
phpmysqldatabase
alice
alice

Reputation: 101

Joining two similar datatabases on same server

I have two databases with same schema. I need to perform a join on both tables in order to get page results. I am clueless about joining databases. More so since my existing query has joins within first database.

This is my query:

$base = mysql_query("SELECT DISTINCT page.path, 
                page.site_id, 
                (SELECT metadata_custom.value 
                 FROM   metadata_custom 
                 WHERE  field = 'academic search title' 
                        AND page.id = metadata_custom.page_id) AS value, 
                page.id 
FROM   page 
       INNER JOIN metadata 
               ON page.metadata_id = metadata.id 
       INNER JOIN metadata_custom 
               ON page.id = metadata_custom.page_id 
WHERE  field = 'academic search keywords' 
       AND value LIKE 'undergraduate%' 
ORDER  BY value ");

I need same result but after this first database is joined with second. Any pointers?

Update: There are two connections here, with same host and different userid and password. I am not getting any connection error though but script is not returning anything. Must there be an error here:

    $base = mysql_query("select distinct page.path,page.site_id,
 (select metadata_custom.value from db1.metadata_custom where field='academic search title' and page.id=metadata_custom.page_id) 
 AS value,
page.id from db1.page inner join db1.metadata on db1.page.metadata_id=db1.metadata.id inner join db1.metadata_custom on db1.page.id=db1.metadata_custom.page_id where field='academic search keywords' and
value like 'undergraduate%' 

UNION

select distinct page.path,site_id,(select metadata_custom.value from metadata_custom where field='academic search title' and page.id=metadata_custom.page_id) AS value,
page.id from db2.page inner join db2.metadata on db2.page.metadata_id=db2.metadata.id inner join db2.metadata_custom on db2.page.id=db2.metadata_custom.page_id where field='academic search keywords' and
value like 'undergraduate%' 

 ORDER BY value", $connection);

db1 and db2 are the two databases here

I used same connection link since I read somewhere that it is connection to server and can be used to access any database on the same server.

Thank you for your help and time, much appreciate it.

Upvotes: 0

Views: 60

Answers (1)

spencer7593
spencer7593

Reputation: 108460

Sounds like you want a UNION or UNION ALL set operator. That operator combines the rows from two different SELECT statements. The two SELECT statements have to return the same number of columns and the same datatype for each column.

For example:

SELECT 'foo' AS a, 1 AS b
 UNION ALL
SELECT 'bar', 3

To query a different database, you need to qualify the table references with the database name

SELECT t.mycol
  FROM mytable t
 UNION ALL
SELECT o.somecol
  FROM otherdatabase.mytable o

Given that you are using the DISTINCT keyword in your original query, you can use the UNION operator, which eliminates duplicate rows. (The UNION ALL operator does not perform the extra step of identifying and removing duplicate rows.)

FOLLOWUP

A MySQL query can't reference two different connections; a single query can run in one connection only. (Obviously, you can run two separate queries, against two different connections.)

You said 'another databases', I took that to mean another database on the same MySQL instance.

A single connection can access any database that it is granted privileges on.

In order for a user (e.g. user1) to be able to select from tables in each database, that user has to be granted SELECT privilege on the tables. To grant SELECT privilege on all tables in databases db1 and db2 to user1:

GRANT SELECT ON db1.* TO user1@'%' ;
GRANT SELECT ON db2.* TO user1@'%' ;

Then user1 can run a query that references tables in both databases:

SELECT t1.id
  FROM db1.mytable t1
 UNION ALL
SELECT t2.id
  FROM db2.mytable t2

As a convenient shorthand, we typically do not qualify table references with the database name, and instead rely on the current database being set via a "USE db1;" statement or equivalent.

For example, mytable here is equivalent to a reference to db1.mytable, the table identified as mytable in the db1 database, (assuming obviously that the USE statement succeeds.)

USE db1;
SELECT t1.id
  FROM mytable t1

This way, we can run an identical statement against a different database, with no change to the statement, all we change is the current database:

USE db2;
SELECT t1.id
  FROM mytable t1

In this case, mytable is a reference to db2.mytable, the table in the db2 database.

To reference tables in two different databases, we have to qualify at least the reference to the table in the "other" database.

Which is why we had to write:

SELECT t1.id
  FROM mytable t1
 UNION ALL
SELECT t2.id
  FROM otherdatabase.mytable t2

Note, again, that otherdatabase here is the name of another database on the same MySQL instance. The currently connected user has to have been granted SELECT privilege on otherdatabase.mytable (It could be through a "grant select" on every table in otherdatabase, or it could be global privilege to select from any table in any database.)

Upvotes: 2

Related Questions