Balderick Posted April 16, 2017 Share Posted April 16, 2017 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? Link to comment Share on other sites More sharing options...
Ingolme Posted April 16, 2017 Share Posted April 16, 2017 (edited) 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 April 16, 2017 by Ingolme Fixed SQL Link to comment Share on other sites More sharing options...
Balderick Posted April 16, 2017 Author Share Posted April 16, 2017 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? Link to comment Share on other sites More sharing options...
Ingolme Posted April 16, 2017 Share Posted April 16, 2017 "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. Link to comment Share on other sites More sharing options...
Balderick Posted April 17, 2017 Author Share Posted April 17, 2017 (edited) 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 April 17, 2017 by Balderick Link to comment Share on other sites More sharing options...
Ingolme Posted April 17, 2017 Share Posted April 17, 2017 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. Link to comment Share on other sites More sharing options...
Balderick Posted April 17, 2017 Author Share Posted April 17, 2017 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted April 17, 2017 Share Posted April 17, 2017 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. Link to comment Share on other sites More sharing options...
Balderick Posted April 18, 2017 Author Share Posted April 18, 2017 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? Link to comment Share on other sites More sharing options...
dsonesuk Posted April 18, 2017 Share Posted April 18, 2017 You did use ingolmes corrected code? There should be a unique singular record from both with matching id. Link to comment Share on other sites More sharing options...
iwato Posted April 18, 2017 Share Posted April 18, 2017 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now