Re: Use of more than 1 database, is it possible?

Yes there is a way to use tables from more than one database Example: you have two databases in the same server called db1 and db2 Let's say you have one table in each database t1 in db1 and t2 in db2 ... in t1 you might want to see field f1 of table t1 and field f2 of table t2 you would write your select statement like this : SELECT f1, f2
FROM db1.t1, db2.t2;

Now let's get into something more complex... you want to get info fromt databases that are not located in the same server, now we are getting into something called 'Distributed Databases'. You can even have them in different geographical places. I don't clearly remember the exact SQL Syntax to create these kind of databases but here's the thing behind them : You have different tables and databases that you can access from a site using links that you have to define previously. For example.. you could be in a HQ of a big company in NewYork and would want to retrieve some info about your clients from two subsidiaries sites you have in Chicago and Miami but also from NewYork.
In your DBMS in NewYork you would have defined a link called 'chicago' wich would point to the DBMS in Chicago and another called 'miami' that would point to the DBMS in Miami. You can also create a link that points to the local database called 'newyork' (this is useful when you want to create generic scripts that should be ran anywhere... I will talk you later about this)

Assuming that in each site you have a table called 'client' you would do something like this to gather all the info in NewYork : SELECT firstName, lastName, adress FROM client // the link 'newyork' is optional here
UNION
SELECT firstName,lastName,adress FROM client_at_chicago UNION
SELECT firstName,lastName,adress FROM client_at_miami;

See ?

I've worked with this in the past using ORACLE 8.0 but I don't clearly remember how to create the links.

You can also have a table in NewYork or in the other subsidiaries called 'all_clients' that is always going to represent the info which exists in the three places so that the distribution of the databases is transparent for the user. You do these using VIEWS. Again.. I don't clearly remember the exact syntax.. but it would go something like this :

CREATE VIEW all_clients AS (
SELECT firstName, lastName, adress FROM client_at_newyork UNION
SELECT firstName,lastName,adress FROM client_at_chicago UNION
SELECT firstName,lastName,adress FROM client_at_miami);

In NeyYork you can now see the info related to the local clients with SELECT *
FROM client;

and the info related to all the clients with SELECT *
FROM all_clients;

See ?

REM: I can execute this SQL code in any of the three sites and it's going to work. If you don't create a link that points to itself you have to do specific scripts for each site... try to figure out how that happens.

In my example we created a unified table from identical fields found in distributed databases. This is called 'horizontal agregation' (I'm not sure about the term : I learnt all this stuff in french...) but we can also have 'vertical agregation'. Example :
You're a car inssurance company. You have three sites. In one site you manage all the clients personal info, in another site you manage contracts infos and in another one you manage car values infos. You can have something like this :
NewYork : Client[clientId,firstName,lastName,adress]; Let's say you only have a contract per client Chicago : Contract[contractNum,clientId,contractDate,carType]; Miami : Car[carType,insurranceValue];

You could create a view in any site that would retrieve info about a client, with the contract Date and the inssurance Value doing this :

CREATE VIEW contracts AS
SELECT firstName, lastName, adress, contractDate, insurranceValue FROM cl client_at_newyork, co contract_at_chicago, ca car_at_miami WHERE cl.clientId = co.clientId
AND co.carType = ca.carType;

Well... I'm not sure this is the exact syntax to do it... but well there's the idea. So.. YES IT IS POSSIBLE TO USE TABLES FROM MORE THAN ONE DATABASE.. but you need to have a DBMS that supports distribution.

Panda <pandabearz_at_hotmail.com> wrote in message news:a83f5b83.0108141356.6ca32946_at_posting.google.com...
> Hi, I just wanted to know if there is any way to use tables from more
> than 1 database. Rather than write SQL statements using tables from 1
> database, is there a way to write SQL statements using tables from
> more than 1 database. Please explain me and thank you for your help.
>
Received on Wed Aug 15 2001 - 07:11:07 CEST