databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

databases

Author

Topic

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2010-03-25 : 12:40:44

Hello guys,

i have two databases. one is db_sales and another db_inventory

db_sales - table is Sales_tab and fields are - sales_id, cust_id, custn_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_phone, cust_email fileds

db_inventory - table is Inventory_tab and fields are - inv_id, inv_name, inv_title, inv_desc, inv_email, inv_zip

notes:- cust_zip and inv_zip will have same values and cust_email and zip_email will also have some emails same...so how can i join two databses on these two same fields?

may be cross join databses. not sure. please, can anyone help?

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:43:24

i think they're tables not databases


select *
from db_sales s
join db_inventory i
on i.inv_zip = s.cust_zip
and i.inv_email = s.cust_email

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 12:43:53

Are you sure you have 2 databases, and not 2 tables within the one database?

There are 10 types of people in the world, those that understand binary, and those that don't.

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2010-03-25 : 12:45:10

i have modified my question. there are two databases and two different tables. thanks for ur reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:47:37

quote:
Originally posted by sqlhelp14

i have modified my question. there are two databases and two different tables. thanks for ur reply.

then :-


use [db_inventory]
go

select *
from db_sales..salestable  s
join inventorytable i
on i.inv_zip = s.cust_zip
and i.inv_email = s.cust_email

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-25 : 12:49:33

1) You are saying "databases" but you are listing columns as if they (db_sales and db_inventory) were tables.
2) If they really are databases on the same server, you can use a three part name consisting of the database.schema.table

Example:[CODE]
select s.custn_name, i.inv_desc
from sales s
inner join
db_inventory.dbo.inventory i
on s.cust_zip = i.inv_zip
and s.cust_email = i.inv_email[/CODE]

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2010-03-25 : 12:54:21

first of thanks for ur replies visakh and bustaz good catch. its two databases but in different servers so is it possible to do query? or not with two different servers and two different databases?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 13:02:35

quote:
Originally posted by sqlhelp14

first of thanks for ur replies visakh and bustaz good catch. its two databases but in different servers so is it possible to do query? or not with two different servers and two different databases?

Thanks.

its possible using distributed queries like OPENROWSET or using linked server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2010-03-25 : 13:14:35

how can i do that in my query using liked server can you tell me plz> thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 13:17:15

see

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources