sql query to select a value from one database

Hai,

I have two database and I want to select one value from one of the databases.... for that I want to pass one value and if that value is stored in the database I want to pick the id representing the value in the database.

that means the operation is that.....

first I select a row of data from one database by using a user control... in that row there is a value (example "apple") and I want pass this value("apple") to the second database... in the second database the value("apple") having a id (example "australian") I want that the query search for that id("australian") and show that in the text box.

Please help me.... thanks to all advance....

example

first database

id     name      details
1      apple     sweet
2     orange     sweet

second database

id                  name          details
Australian        apple         sold
Indian            banana        sold

Imagine that there are the two databases.... using a user control I select first row from first database and I want to pass that value apple to second database and find out the id australian from the second database and show that in a text box....

thank you.........开发者_开发问答

You can do a join between the two databases as long as you use the fully qualified prefix for each one.

I think you should go for the join , your query should look something like this

SELECT SecondDataBase.TableName.Id  
FROM   FirstDatabase.TableName INNER JOIN
       SecondDataBase.TableName ON FirstDatabase.TableName.["Column contains Apple"] = SecondDataBase.TableName.["Column contains Apple"]

Fully qualified table name, which includes server name, db name, schema and table (e.g. MySqlServerInstance1.mydb1.dbo.table1) name will definitely work as long as one database server has a registered reference within a calling DB server. See this for things you have to do if you are using MS SQL Server: http://msdn.microsoft.com/en-us/library/ms188231.aspx

In MSSQL you normally reference a table using SchemaName.TableName

dbo.Fruit

The database is automatically determined by your connection string. FirstDatabase

So when you use dbo.fruit, the server automatically appends the database name to the table like

FirstDatabase.dbo.Fruit

If the user account has permission, you can select from a completely different database by specifying the database

SecondDatabase.dbo.FruitSales

To take it even further you can select from an entirely different SQL server if you have set up a linked server by specifying the linked server name like

SecondServer.ThirdDatabase.dbo.FruitShipping

So you can join between a table in your database and a table in your second database like

SELECT * 
FROM FirstDatabase.dbo.Fruit AS F INNER JOIN 
     SecondDatabase.dbo.FruitSales AS FS ON F.Something = FS.Something

But you can even join between a table in your database and a table on a different server like

SELECT * 
FROM FirstDatabase.dbo.Fruit AS F INNER JOIN 
     SecondServer.ThirdDatabase.dbo.FruitShipping AS FS ON F.Something = FS.Something