Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to join two tables from 2 different databases? 2

Status
Not open for further replies.

bsarman

Technical User
Feb 10, 2003
73
US
I'm trying to join 2 tables from 2 different databases.

table1 is called "sale" (in "convert" database)
table2 is called "category" (in "propertydb" database)

The matching columns are
s.subcategoryid = c.categoryname

I tried something like this but it doesn't work.

SELECT subcategoryid
FROM sale
WHERE subcategoryid in
(USE propertydb
SELECT
subcategoryID,
className,
categoryName
FROM
category)

What's the correct syntax to join these two tables?

Thank you.
Alex

 
Just prefix the table with database name. Try this:

SELECT sale.subcategoryid
FROM sale
WHERE subcategoryid in
(SELECT
subcategoryID,
className,
categoryName
FROM
propertydb.dbo.category)

Hope this helps.
 
Use the three-part object name.

dbname.dbo.tablename

SELECT subcategoryid
FROM sale
WHERE subcategoryid in
(SELECT subcategoryID
FROM propertydb.dbo.category)

Either of the following will be more efficient than using WHERE IN.

SELECT subcategoryid
FROM sale a
JOIN propertydb.dbo.category b
On a.subcategoryid = b.subcategoryid

SELECT subcategoryid
FROM sale
WHERE Exists
(SELECT *
FROM propertydb.dbo.category
WHERE subcategoryid = sales.subcategoryid)
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:
 
Thank you very much for the help. With the suggestions I was given, I used the syntax below and got the results I needed.

SELECT
s.subcategoryid,
c.className,
c.categoryName
FROM
sale s
INNER JOIN propertydb.dbo.category c
ON s.subcategoryid = c.subcategoryid

Even though this worked for me, out of curiousity I would like to ask this:
Is there a way to retrieve the same information using;
"WHERE Exists" statement?

In other words, can I include the
-subcategoryid (from the convert db, sale table)
-className and categoryName (from the propertydb, category table) in the first SELECT statement to include these fields in the result of the query?
Something like;
SELECT
s.subcategoryid,
c.className,
c.categoryName
FROM
sale_templatestanfieldpersonal s
WHERE EXISTS
(..?

Thanks again for your help.
Alex

 
I get it.
So if I want to retrieve other fields from the other database as well, then I'd need to use three-part object name with a join like I used.

Thank you very much indeed.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top