Advanced SQL Query

Question

Advanced SQL Query

sql guy-862983

Valued Member

Points: 69
More actions
September 15, 2008 at 9:53 am

#212689

i want to query two different database (both of which are sql server) in a single query ?. i've tried different things but unable to get the semantics right?. can anyone please help me

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post what you've tried please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

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

If they are on the same server, you need to use a "3-part name" in the query.

If they are on different servers, you need to set up a linked server on the one you'll do the query from, and then use a "4-part name".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sql guy-862983 Valued Member Points: 69 More actions · Answer 3

they are on the same server and this is what i tried. i am getting a result but its gibberish!! i am just starting off sql so may be i am not getting the big picture

SELECT tablename.columnname

FROM db1.tablename1, db2.tablename2

WHERE tablename1.columnname1 = tablename2.columnname2

noeld SSC Guru Points: 96590 More actions · Answer 4

sql guy (9/15/2008)
they are on the same server and this is what i tried. i am getting a result but its gibberish!! i am just starting off sql so may be i am not getting the big picture
SELECT tablename.columnname
FROM db1.tablename1, db2.tablename2
WHERE tablename1.columnname1 = tablename2.columnname2

SELECT t1.columnnameX, t2.columnameY

FROM db1.[schema_name].tablename1 t1, db2.[schema_name].tablename2 t2

WHERE t1.columnname1 = t2.columnname2

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

You might also want to check the correct syntax for an inner join!!! That'll save you a lot of troubles down the road.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

sql guy (9/15/2008)
they are on the same server and this is what i tried. i am getting a result but its gibberish!! i am just starting off sql so may be i am not getting the big picture
SELECT tablename.columnname
FROM db1.tablename1, db2.tablename2
WHERE tablename1.columnname1 = tablename2.columnname2

That should be written as follows:

SELECT d1t1.columnnames, d2t2.columnnames

FROM db1.dbo.tablename1 d1t1

INNER JOIN db2.dbo.tablename2 d2t2 ON d1t1.ColumnName1 = d2t2.ColumnName2

Inner join rather than joining in the where clause

3 part naming = database.schema.table

aliases to make things easier to read

If you're getting garbage, check that you're joining on the right columns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql guy-862983 Valued Member Points: 69 More actions · Answer 7

hey guys,

Thank you for responding so fast. the inner join works perfectly.

can you also tell me how the query will look if its on a different server, for future reference. it would be of great help !!!

thank you!! i appreciate all your efforts !!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

You'd need to set up a linked server (Books online is your friend)

Assuming you have a linked server called server2 and database2 is on that remote server, the query would look like this:

SELECT d1t1.columnnames, d2t2.columnnames

FROM db1.dbo.tablename1 d1t1

INNER JOIN server2.db2.dbo.tablename2 d2t2 ON d1t1.ColumnName1 = d2t2.ColumnName2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql guy-862983 Valued Member Points: 69 More actions · Answer 9

Thanks a ton Gail. your solution made it so much easier !!! I appreciate it !! Thank you !

Advanced SQL Query

Cookies on SQLServerCentral