CODEWITHSUNDEEP
phpmysqlsqldatabasejoin
Chinmay235
Chinmay235

Reputation: 3414

MySQL How to join two table in two different database?

I have two database chinmay235 and desarrollo_forum

I want to join two table nuke_follow_votes and smf_members using JOIN query.

nuke_follow_votes table exist in chinmay235 and smf_members exist in desarrollo_forum database.

Code : 

<?php
  $dbhost = "localhost";
  $dbuname = "chinmay_db";
  $dbpass = "2014@movie";
  $dbname = "chinmay235";

  mysql_connect($dbhost,$dbuname,$dbpass) or die(mysql_error());
  mysql_select_db($dbname) or die(mysql_error());

  $qry = mysql_query("SELECT a.* FROM chinmay235.nuke_follow_votes a LEFT JOIN desarrollo_forum.smf_members b ON a.user_id=b.id_member WHERE b.user_id IS NOT NULL;") or die(mysql_error());
  $res=mysql_fetch_row($qry);

  echo "Total Record = ".mysql_num_rows($qry);
?>

Output : 

SELECT command denied to user 'chinmay235'@'localhost' for table 'smf_members'

I am successfully connect the chinmay235 and getting the table nuke_follow_votes but problem is unable to connect the second database.

Please help me how can i connect and join this two table and get the data correct data.

Upvotes: 3

Views: 4002

Answers (4)

sajin tm
sajin tm

Reputation: 323

The query seems fine but have you limited the select privilege of desarrollo_forum db?

Try to give privilege to the second db like this, 

GRANT ALL ON desarrollo_forum.* TO chinmay235@'localhost';
FLUSH PRIVILEGES;

Upvotes: 0

Mayuri
Mayuri

Reputation: 412

Syntax for join two tables

SELECT <select_list> FROM Table A.A JOIN Table B.B ON A.key = B.key

As per your requirement you can use LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN,INNER JOIN

Upvotes: 0

SagarPPanchal
SagarPPanchal

Reputation: 10111

MYSQL Server allows you to join tables from different databases as long as those databases are on the same server. The join syntax is the same; the only difference is that you must fully specify table names.

Let's suppose you have two databases on the same server - Db1 and Db2. Db1 has a table called Clients with a column ClientId and Db2 has a table called Messages with a column ClientId (let's leave asside why those tables are in different databases).

Now, to perform a join on the above-mentioned tables you will be using this query:

Assuming the account has appropriate permissions you can use:

    SELECT ...
  FROM A.table t1
  JOIN B.table2 t2 ON t2.column = t1.col

Upvotes: 0

Erico
Erico

Reputation: 1461

This is just a permission issue.

It seems your user can't select table on the database desarrollo_forum.

Check the permissions for your user with the following command.

SHOW GRANTS FOR 'chinmay235'@'localhost';

You can add grants with the GRANT command.

Upvotes: 1

Related Questions