Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: Multi Table Query

    Does anyone know how to create a query using tables from different sql server database's? looking for the simplest solution.

    I have two databases in sql server. Both are in the same "server registration"
    How do i reference a table in another database?

    Do i do something like this?

    SELECT Database1.Table1.Fields, Database2.Table1.Fields
    FROM Database1.table1.PKField INNER JOIN Database2.table1.FKField;

  2. #2
    Join Date
    Feb 2002
    Location
    India
    Posts
    38
    You may try this
    Code:
    SELECT Database1.dbo.Table1.Fields, 
    Database2.dbo.Table1.Fields FROM 
    Database1.dbo.table1.PKField INNER JOIN
    Database2.dbo.table1.FKField
    Last edited by buddu; 06-25-02 at 01:15.
    -- Prasad --

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    you might also want to look at the OPENQUERY funtion. buddu's suggestion will work but you can generate a buch of unwanted I/O if the table on the remote server is big.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You only need to do the full naming convension in the FROM clause

    Code:
    SELECT x.column1,
           x.column2,
           y.column1,
           y.column3
    FROM   database1.dbo.tableX  x
    JOIN   database2.dbo.tabley  y
    ON     x.pk    = y.pk
    MCDBA

  5. #5
    Join Date
    Mar 2004
    Location
    QC, Ph
    Posts
    1

    hey people

    can anyone help me please...
    what query/syntax could i use for retrieving fields from various tables in Access for my one FORM.?
    Ive tried using every possible codes but then it didn't work out.
    SHould i declare diffrent recordsets for accesing this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •