September 15, 2008 at 9:53 am
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
September 15, 2008 at 10:14 am
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
September 15, 2008 at 10:52 am
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
September 15, 2008 at 11:17 am
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
September 15, 2008 at 11:44 am
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 pictureSELECT 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
September 15, 2008 at 11:46 am
You might also want to check the correct syntax for an inner join!!! That'll save you a lot of troubles down the road.
September 15, 2008 at 11:46 am
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 pictureSELECT 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
September 15, 2008 at 12:05 pm
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 !!
September 15, 2008 at 12:13 pm
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
September 15, 2008 at 12:37 pm
Thanks a ton Gail. your solution made it so much easier !!! I appreciate it !! Thank you !
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy