Advanced SQL Query

  • 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

  • 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
  • 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

  • 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

  • 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

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

  • 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
  • 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 !!

  • 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
  • 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