JOIN statements - 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

General SQL Server Forums

New to SQL Server Programming

JOIN statements

Author

Topic

Sean
Starting Member

5 Posts

Posted - 2007-08-13 : 14:07:31

I know that you can use the JOIN's to join together data from different tables within a database.

But my question is, can you join data from different databases?

i.e. get a users name from database A using an id you got from database B

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 14:11:41

quote:
Originally posted by Sean

I know that you can use the JOIN's to join together data from different tables within a database.

But my question is, can you join data from different databases?

i.e. get a users name from database A using an id you got from database B

Yes, with the 4 part name.


SELECT A.column, b.column2
FROM TableA
Join ServerB.db2.dbo.TableB B ON A.someId = B.SomeOtherId

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Sean
Starting Member

5 Posts

Posted - 2007-08-13 : 14:54:47

Can you give an example, not just a syntax example?

Would be great, thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 14:57:21

Are the 2 databases on the same server or different servers?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:58:47

the syntax is very clear. why don't you post the names of your databases and tables and column names and what result you want. then you can get the exact query u are looking for. you do not have to post real names you can post pseudo names as well if you want. whatever makes you feel comfortable. or try to replace them in dinakars syntax

Ashley Rhodes

Sean
Starting Member

5 Posts

Posted - 2007-08-13 : 15:09:01

quote:
i.e. get a users name from database A using an id you got from database B

This more of a question to get the four part name answer (thanks for that). Now that i have learned about, the syntax is:

ServerName.DatabaseName.DatabaseOwner.TableName

Which for me (because im testing localy) converts to:

localhost.myDB.dbo.myTABLE

The only thng is that i don't know what my dbo would be. Would it be my connection username?

P.S. the Databases are on the same server

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 15:38:10

if they are on same server then you do not even need to mention the server name just use the 3 parts. dbo is the database owner name.

so just use DBO and nothing else. its not connection name, it can be the object owner name. it will be dbo so just leave it like that.

use this

DatabaseName.Dbo.TableName

Ashley Rhodes

Sean
Starting Member

5 Posts

Posted - 2007-08-13 : 15:41:28

i used this code in php:

$line = mysql_fetch_assoc(mysql_query("SELECT * FROM portal_members.dbo.tbl_information"));
echo $line['member_id'];

I get this error:

quote:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\site\page.php on line 51

Which simply means that it can't connect to the table

Is this right code correct for php and mysql, or does it need to be changed? Show me some moderations

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 16:12:41

You are in the wrong place. This site and these forums are primarily for Microsoft SQL Server database.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Sean
Starting Member

5 Posts

Posted - 2007-08-13 : 16:12:57

OK i managed to get it from a four part name to a two part name.

Syntax im now using is:

database.table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources