SELECT From Different Databases/servers Tables
I can't find the exact MySQL instructions to get data from tables located on a different database in the same engine and/or on different databases in different engines. I am working to a migration from Informix. In Informix a table name is defined as:
<tbl_name> (as usual)
or
<db_name>:<tbl_name> (same engine, different db)
or
<db_name>@<server>:<tbl_name>
(different engine, different db)
How can, if possible, replicate this syntax in MySQL ??
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
MySQL Databases And 2 Servers?
We are planning to reprogramm our search machine and website. Since we have call agents who query the same database as the customers do, we had the idea of splitting the database to 2 servers. The call agents can update customer data with our intern webinterface, which not only concerns tables that are for the call agents, but also concerns tables of the search machine, holding company data such as address, search words, etc. O.k., now my questions. Since the customer himself can administer his own data, as well as the call agent (or media agent), I was thinking of doing following: * I have 2 servers. Server 1 is responsible for the search machine and just serves customers searching for a company or for products. The MySQL database is there just for querying. * Server 2 serves call agents, as well customers who want to change their data, reading and writing. * Since the search machine should of course show updated data the call agent or the customer have eventually changed, the data from server 2 must be synchronized with the MySQL databese of server 1. (e.g. at midnight) Questions: ------------- 0. Is the above described the right way? 1. What do I have to consider when planning the database structure? 2. How do I synchronize? Can I copy specific tables from one server to another using cronjobs (crontables)? Since this is absolute newland to me, how? Any expert knowledge or experience with MySQL replication or MySQL clusters? I think replication is just thought for mirroring, or am I mistaken?
MySQL Databases And 2 Servers?
We are planning to reprogramm our search machine and website. Since we have call agents who query the same database as the customers do, we had the idea of splitting the database to 2 servers. The call agents can update customer data with our intern webinterface, which not only concerns tables that are for the call agents, but also concerns tables of the search machine, holding company data such as address, search words, etc. O.k., now my questions. Since the customer himself can administer his own data, as well as the call agent (or media agent), I was thinking of doing following: * I have 2 servers. Server 1 is responsible for the search machine and just serves customers searching for a company or for products. The MySQL database is there just for querying. * Server 2 serves call agents, as well customers who want to change their data, reading and writing. * Since the search machine should of course show updated data the call agent or the customer have eventually changed, the data from server 2 must be synchronized with the MySQL databese of server 1. (e.g. at midnight) Questions: ------------- 0. Is the above described the right way? 1. What do I have to consider when planning the database structure? 2. How do I synchronize? Can I copy specific tables from one server to another using cronjobs (crontables)? Since this is absolute newland to me, how? Any expert knowledge or experience with MySQL replication or MySQL clusters? I think replication is just thought for mirroring, or am I mistaken?
Scripts, Databases And Servers
Is it possible to have a script in server1 calling a database located in server2 (both remote servers)? I made a try but it didn't work. PHP Code: $hostname_owner = "mysql.server.com"; // this is remote $database_owner = "database"; $username_owner = "owner"; $password_owner = "passw"; $owner = mysql_connect($hostname_owner, $username_owner, $password_owner) or trigger_error(mysql_error(),E_USER_ERROR);
Moving Databases Across Servers
I just found out that my host is running an old version of mysql, and I think that's why alot of the things I've written aren't working. I've got host space at another server with a newer version of MYsql, and I'd like to transfer most of my work over there. ...But I don't want to have to completely rebuild all my databases and their entire contents.Is there some simple way anyone knows to download the tables & contents of a database from one host and then put it back up on another?
Can You RIGHT JOIN A List? (from Two Different Servers And Databases)
One database provides a list of items to be used in a query against another database's table. The two different database are on two different servers. Can I get the list from one query and use it as a LEFT JOIN query in another? Like: select items from database1.table1 <loop through in php to get list> select fish from database2.table2 RIGHT JOIN <somehow add list here from table1> I've tried using the list in an IN where clause but I need ALL those in the list and only those from table2 where there's a match, else, NULL.
Compile Data From MySQL Tables On Different Servers
I'd like to retrieve data from MySQL tables on websites A, B, and C, to display on website D -- is this possible? Basically, from site D, I'd like to compile the last entries in specific tables in sites A, B and C, and show them on a "summary page". Do I need to import the data into a local (site D) table, or can I retrieve the data from A, B and C on the fly?
100 Databases, 4 Tables Each, Or 1 Database, 400 Tables
I was able to build, tweak and do whatever necessary to establish a rather different project but am worried that it may sooner or later crash due to idiotic programming / database usage. In essense I took 1 database with 4 tables and duplicate it 100 times BUT because I need to connect to the different tables within the php script and am not experienced enough to created nested database connections, I duplicate the 400 tables within the same database. What I want to know from a MySql perspective, would it make any difference if the script "play around" with 1 database connection and 400 tables or would 100 databases with 4 tables each be better? (Maximum database queries to only 17 tables at a time)
Databases/Tables
I was thinking, that trying to make a database/table and if it failed then it already existed but then i thought it might overwrite instead so i figured i would ask before i tried.
Two Tables From Two Databases
I have two databases in mysql server. Say I have a table called 'table1' in database A and 'table2' in another database B. I want get two coloumns' data (colA1, colA2) of table1 into their corresponding coloumns (colB1,colB2) in table2. How would I go about doing this using sql query?
Select Databases
Is there a query by which I can show only those databases that have a substring "abc" in them?
Combining Two Databases/tables
Im soon oging to be setting up a website which will host forums and auctioning software, the problem I have is that I dont want to have people having to register for each one. I would like it so that once they register they can use the same login name for each feature.
Joining Tables That Are In Different Databases
How do I join two table that are in two different databases? In MSSQL or Oracle, I would have done something like "SELECT * FROM db1..table1 t1, db2..table2 t2 WHERE t1.id = t2.id". Unless my memory failed me anyway. Is there an equivilant syntax for MySQL?
Moving Tables Between Databases
I only have access to my phpmyadmin on a website, How can I transfer tables from one database to the other? is there any sql command for that in mysql?
Join 2 Tables From Different Databases
I need to join 2 tables from different databases for creating one SELECT query and get data from both tables. Mechanism LEFT JOIN don't allow to do this or may be I don't see it. Can I join and if I can, how can I do this?
Can Connect And List Databases But Not Tables
I have two mysql servers (Centos 4, MySQL 4.1.20-log) and I need to perform admin from a remote server. I've added in both servers a user with all privileges GRANT ALL PRIVILEGES ON *.* TO 'user'@'phpmyadminserver' IDENTIFIED BY PASSWORD 'XXXXXXXXXXX' In the first one I can connect and list databases and tables. In the second one I can only connect and list the databases. A show tables command shows me : show tables; Empty set (0.01 sec) Any ideas?
Many Databases With 1 Table Or 1 Database With Many Tables
I've to manage many 'table' (having same scheme) on the same server. And I ask myself what could be the best to do (and if you know, why) : Creating as many database (the name would be a 8byte int value (converted to a string)) as necessary, all with the same table struct (and table name), or create 1 database and in it create also as many table as necessary (so also a 8byte int value as name) ? Has mysql some limitations on that (database/table) and what about the performance issue ?
Select From Two Different Tables Depending On Result Of First Select
I have three tables A contains three keys (there is other stuff but it's not relevent to this problem): it's own pk, fk to table B fk to table C if the table B fk is not zero then select various values from table B, if the table C fk is not zero then select varios values from table C The two fk are never both zero and never both non zero The best query I've got so far is select distinct l.job_parts_id, l.part_id, l.oil_id, l.quantity, l.purchase_price, l.sale_price, p.part_no, p.description, p.supplier_id, o.part_no, o.description, o.supplier_id from job_parts l, parts p, oil o where task_id = 3 and p.part_id = l.part_id or o.oil_id = l.oil_id order by job_parts_id; the problem at the moment is that when the fk to table c is zero, the result set still contains the values from it's selected fields, I'm trying to work out how to use an IF statement to sort this out,
Two Servers
I run two MySQL servers on Windows with the two services. I read in the docs that it exists a way to identify the servers in my.cnf by groups and about a tool from Linux to manage the groups. How can I start a server on Windows through the my.cnf/my.ini 's groups?
Different Db Servers
db masters, Can I use databases from different servers ? I mean, for example, I have two sites : www.site1.com --- hosted by providerA www.site2.com --- hosted by providerB The site2 uses databases from providerA for running queries or updating tables
2 Mysql Servers
I have a Mysql server lets call it "Production", but also do lots of development when traveling (offline) on my laptop, where i also have mysql instance installed + php + apache .... both MySql are 5 and same build running on windows. What would be the best way to "replicate" Mysql from my laptop to the server when I get connected. One way solution (from Laptop to Server) would be great, but both ways (whichever is newer gets replicated would be excellent). thought about replication but i undertood that the master 'version' will always be writen to slave. We need to consider any kind of changes, table changes, dbs change, users, data, etc... Hope you can give me a light, it is not a large amount of data, and i would not mind a very manual process, as long i can script it or if it is a few steps only.
Redundant Servers
So physical Server A has a mysql database running on it, and physical Server B has an identical database running on it. so if one of the physical servers goes down, I can get information from the other mysql server. I also want them to keep identical information on each other, so if a user updates his password, it's updated in both databases in real time....and when one of the servers comes back online, it gets rebuilt by server that stayed up.
Multiple Servers
How do I configure my phpmysadmin to administer mysql databases on multiple servers?
Join Across Servers
Can you join two tables across different servers in mySQL, or am I dreaming of Oracle?
Two Master Servers
I am using two master servers that are synchronized with each other so that if one of them gets disconnected, the other one could take over and no data is lost. Now my problem is doing backup on the servers. Say I am doing backup on Master 1 and after backup, I will delete the data. So, since Master 2 is synchronized with Master 1, will the deleted data be written back to Master 1? My batch file is something like this. echo Executing MYSQL BACKUP mysqldump -uroot -proot --result-file=%SAVPATH%%FILENAME%".sql" database1 echo Executing MYSQL DELETE mysql -uroot -proot datalog < "C:Program FilesMySQLMySQL Server 5.0inscript.sql" Script.sql will call the command "DELETE from table1" for me.
Slave Servers
i have do replication with 1 master server to 3 slave servers. And i want to grant the 3 slave servers only select option for all user;
Running Two Servers
--=__Part1A441A35.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit I am looking into installing 2 mysql servers on the same windows 2000 PC. From what I have found, it is usually possible to have 2 different versions on the same machine. (Usually one for testing and the other for production). However, I am trying to run 2 production servers, one for personal family info, and the other for business opportunity. Being that I just started using mysql, is it possible then to have the same version installed twice, or how do I accomplish my task?
Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time. I have to decide if I should use : 1)one database with one table for all users. 2)one database for each user. 3)one database with one table for each user. I would really appreciate if you can tell me what choice is best and why.
Separate Databases Vs Separate Tables
I have a question regarding setting up MYSQL. I'm a old dBASE programmer from the 1980's. I know about relational database work. What I want to know is what are the advantages and disadvantages in setting up MYSQL in one database with many tables vs separate databases with a few tables in each database. It would seem more advantageous to work with all the tables in one database.
Select From 2 Or More Tables
I need to run a select quer against 2 or more tables and return rows with data from all tables, but if data does not exist in one table, no data is returned from the other tables. How can I ensure that a row is returned even if one table does not contain data corresponding to the other tables? For example: lets say I got two tables: "users" and "user_groups". "users" contains the columns; id and name, and the "user_groups" contains the columns user_id and group_name. I run a select query as follows: SELECT users.name, user_groups.group_name FROM users, user_groups WHERE users.id=user_groups.user_id This works great if a user_group exists for every user. But if a user_group does not exist, then the user's name is not returned at all, even though it does exist. So how can I make it return the data even if the user group does not exist??
Select From 3 Tables
I need some help in my statistic,this is my bd tables groups +------+-------+ | descr | image | +------+-------+ users +------+ | groups | +------+ inquiry of 2 tables PHP SELECT descr, image, COUNT(users.groups) as num FROM groups LEFT JOIN users ON users.groups = groups.id GROUP BY descr ORDER BY num ; statistic example ---------+-----------------------+ | Group | Users in group | +--------+-----------------------+ | FunTV | 5 | +--------+-----------------------+ | M-Zone | 15 | +--------+-----------------------+ | Bt-Mov | 3 | +--------+-----------------------+ And now i want add new table torrents +------+ | grupa | +------+ That the statistics would be those +--------+-----------------------+-------------------+ | Group | Users in group | Torrents | +--------+-----------------------+-------------------+ | FunTV | 5 | 9 | +--------+-----------------------+-------------------+ | M-Zone | 15 | 18 | +--------+-----------------------+-------------------+ | Bt-Mov | 3 | 6 | +--------+-----------------------+-------------------+ Question in how me to make inquiry of 3 tables (users,groups,torrents) ? this PHP SELECT descr, image, COUNT(users.groups) as num FROM groups LEFT JOIN users ON users.groups = groups.id GROUP BY descr ORDER BY num ; + this PHP SELECT descr, image, COUNT(torrents.grupa) as grupa FROM groups LEFT JOIN torrents ON torrents.grupa = groups.id GROUP BY descr ORDER BY num ;
Need Help On A Select From 2 Tables
I am trying to get information from 2 tables in one select query. Have 2 tables. Lets call them rip_people and rip_people_comments Basically if you looked at d i g g.com each "posts" has a comment counter on the lower part of the post that shows how many times the post/**** has been commented. Well this is almost exactly what I am having trouble with. I am selecting 15 posts per page but I need somehow to get the comments count along with the 15 posts. This is what I have so far and it lists the same posts because they have more than one comment. PHP SELECT r.rip_people_id, r.title, r.message, r.image, r.video, r.date, c.comment FROM duo_rip_people AS r LEFT JOIN duo_rip_people_comments AS c ON r.rip_people_id=c.rip_people_id ORDER BY r.date DESC
Select From 2 Tables...
I know there are loads of examples out there but im a complete novice and really dont understand code unless someone explains it to me.... Trying to figure out how to do this: $query = "SELECT * FROM story WHERE storyID='" . $_REQUEST["storyID"] . "' AND SELECT * FROM section WHERE sectionID='1a';"; each table (story and section) has totally different columns.... what im trying to do is pull out information mainly from the story table but it also has to match the sectionID from the section table.... both tables have storyID.
How To Select From 2 Tables
I'm building an online database programme (with PHP) where different users can add the contact info of the people in their school. I've set up 2 tables: 'users' (which will hold the login info- user name/ password) and a second table 'data' (where the people can store the information). I have put in a 'users' field in the 'data' table. I want this to relate to the 'id' field in the 'users' table'. In plain English I need to 'select all from the 'data' table where the 'user' field = 'id'field in 'users' table.
Select Using 2 Different Tables
I need to do is select info from table A only if that info is not in table B. Here is what I have: SELECT DISTINCT A.TitleType FROM organizationlots A, TitleCategory B WHERE A.TitleType<>B.TitleType This code selects all from Table A regardless of what is in Table B.
SELECT On Two Tables
I have two tables : Table Event - event_id Table Event_member - Mevent_id - Member_id When a user register to an event, I add the event_id and member_id in the Event_member. But now, I want to select all the events that a USER is not registered. I have done these requests: SELECT "event_id" FROM event, event_member WHERE member_id = USERID AND event_id != mevent_id or SELECT "event_id" FROM event, event_member WHERE member_id = USERID AND event_id <> mevent_id I have edited because I have forgotten "member_id = $userid". I didn't use directly these requests, I convert them in PHP before using them But they didn't work,
How To Select 2 Tables
I need to select 2 tables . The field 'p_description' is not always aviable but i still need to print it to the screen SELECT * FROM task,p_description WHERE ComponentIDLink = 34 OR task.TaskID = p_description.pTaskLink GROUP BY TaskID ORDER BY TaskName If i use AND it works, but there are colums missing Is it possible in a OR that there is a default value ?
SELECT From Two Tables
I have these two tables: "cats" catid INT catname VARCHAR "messages" messageid INT messageparentid INT catid INT messageposted DATETIME messageauthor VARCHAR messagetext TEXT What i need is to get all cats and include the column "messageauthor" from table "messages", but only the latest "messageauthor". That being the the post with the higest "messageid" and the same "catid" as parent table "catid". SELECT * FROM cats ...
SELECT From 2 Different Tables
i'm using this query at the moment: SELECT COUNT(*) AS total FROM buddies WHERE (buddy_from=1 AND buddy_for=2) OR (buddy_for=2 AND buddy_from=1) It results the value '2' (total) if both users are "friends". Now i want to include the following query in the one above: SELECT notification FROM main WHERE userid=2 This must be something like this: Code:
Select With Three Tables
I need combining some table results. I've been able to do this in the past with two tables, but am having some troubles doing it with three. I have 2 reference tables: fav_books fav_authors both have two fields: user_id fav_book / fav_author (depending on the table) I have a third table: users which has a bunch of fields about the user which I need to display, including: user_id However, the fields in 'users' don't need to be searched by, just the info gathered from. What I want to do is compile a ranking of users that match a specific user's interests. So, I want to take the list of fav_books and fav_authors for userA and then get a top 10 list of users that have the most matches of both books and authors. So results like: USER | LOCATION | MATCHES UserB | New York | 5 UserC | San Francsco | 3 UserD | Dallas | 2 Are what I'm looking for. Location (and other fields) would come from 'user', and matches would come from a count/group by of user_id for results from both fav_books and fav_authors. I can do it if I just search fav_books OR fav_authors, but not both.
Select Using Several Tables
I can work around this problem by making several select's but it's getting a bit of performance issue to me... I have -table1 with fields id (primary key), active -table2 with fields id (not relation to table1), rel (this is the relation to table1 :-), ... There are no indexes in table2 yet. What I try to do is to select records from table2 where rel is certain value & table1 should be active. I tried to do it with: select * from table2, table1 where table2.rel = somevalue and table2.rel = table1.id and table1.active = 1; but it returned actually all the records where table2.rel = somevalue...
2 Tables + Select + Max()?
I have 2 tables like this: http://img240.imageshack.us/img240/8695/bild2np0.png And I'd like to have an output like this: http://img99.imageshack.us/img99/3983/bild5mg9.png So the maximum points for the IDs in t1 should be displayed. The other values not. I tried something with a subselect and max(). But it was not possible for me to generate a valid query for that issue.
Select With Two Tables
I need a query to obtain data in a table ordered by recurrence its field in another table. Example: I've table NAME with 1 field and table AGE with 2 fields. (NAME joseph mark mary joseph tom mary victorya joseph) (AGE joseph 26 mark 32 mary 21 tom 28 victoria 34 ) I'd like obtain: 26 21 32 28 34 Because joseph is 3 times in Names and in age is 26, mary is 2 times in Names and in age is 21, anyother is in Names just one time.
|
TRACKER
