Jump to content
Balderick

mysql join from 2 different databases

Recommended Posts

 

Hi all,

I have a question about join or inner join; not sure in what way it should be used.

I now use 2 queries in 2 different databases (made in phpmyadmin).

    SELECT `title` FROM `writers` WHERE id ='qwert58efedd1979f';

    SELECT `name`, `lastname`, `str`, `nr`,` place` FROM `client` WHERE id ='qwert58efedd1979f';

I would like to make one mysql query and use join to search in 2 tables in 2 different databases.

Can anyone tell how mysql does this?

Share this post


Link to post
Share on other sites

Generally I only join tables in the same database, it's been a long time since I had to join tables from two different databases, but prefixing the table names with the database name and a dot should work.

SELECT `w`.`title`, `c`.`name`, `c`.`lastname`, `c`.`str`, `c`.`nr`, `c`.`place`
FROM `database1`.`client` AS c
JOIN `database2`.`writers` AS w ON w.`id` = `c`.`id`
WHERE `c`.`id` = 'qwert58efedd1979f'

 

Edited by Ingolme
Fixed SQL

Share this post


Link to post
Share on other sites
51 minutes ago, Ingolme said:

Generally I only join tables in the same database, it's been a long time since I had to join tables from two different databases, but prefixing the table names with the database name and a dot should work.


SELECT `w`.`title` `name`, `c`.`lastname`, `c`.`str`, `c`.`nr`, `c`.`place`
FROM `database1`.`client` AS c
JOIN `database2`.`writers` AS w ON w.`id` = `c`.`id`
WHERE `c`.`id` = 'qwert58efedd1979f'

 

Its not clear what is meant with the w and the c. Are this the tables?

And what is client then in database1.client or writers in database2.writers? Is that also a table?

 

 

 

Share this post


Link to post
Share on other sites

"c" and "w" are alias for the tables. You use the AS keyword to shorten the table name so that you don't have to type such a long name so many times.

"client" and "writers" are the two tables from your previous post. I don't know the name of the databases that they are in, so I just used "database1" and "database2" as example names.

Share this post


Link to post
Share on other sites

 

I get an error message (error message 1054) : unknown column w.title in field list.

What should I do to open 2 databases?

I work now in the mysql console, but I'm not sure wether it is possible or not.

Edited by Balderick

Share this post


Link to post
Share on other sites

It sounds like there's no field called `title` in your `writers` table.

I don't know anything about your database structure, so all I can do is make guesses based on what little information you provided here.

Share this post


Link to post
Share on other sites
4 hours ago, Ingolme said:

It sounds like there's no field called `title` in your `writers` table.

I don't know anything about your database structure, so all I can do is make guesses based on what little information you provided here.

Is it necessary to open the 2 different databases or not? How is this done in 1) phpmyadmin, 2) mysql console, 3) php script?

with select database() in the console only 1 database is shown.

 

Share this post


Link to post
Share on other sites

As long as your MySQL user has access to both databases, you can run queries on one or both of them.  You don't have to "open" them, once you connect to the server you can either select a default database, or just include the database name in all of your queries.

Share this post


Link to post
Share on other sites

sorry, I have not been able to solve it already.

i got results and the output was that related to 3 different fields in table one; the record in table 2 was printed 3 times. When I added a record to table 2 this one was also 3 times printed so I bascially got six results.

Is it possible anyway to have 1 columns like a unique number (unique_nr) in both tables and then as a result have f.i. multiple records for table 1 and only 1 for table 2?

And is JOIN the right query or should I use something different.

or (as I'm getting a bit impatient) should I just select the unique number first in table 2 and then use a second separate query to find the record values of table 1?

 

 

Share this post


Link to post
Share on other sites

Is it not this MySQL clause that insures that the records in the two databases match?  

ON w.`id` = `c`.`id`

For, if the id's properly matched there would be no duplication in the joined result, unless, of course, there were duplication in both databases.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×