Re: Query tables across multiple databases with sql

From: Jerry Pisk (jerryiii_at_hotmail.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 16:40:36 -0700

The generic way to reference fields is Database.owner.table. In your case it
would translate to:

SELECT
    [t1].[field1] AS [field1 from database 1],
    [t2].[field1] AS [field1 from database 2]
FROM
    [db1]..[table1] AS [t1]
INNER JOIN
    [db2]..[table1] AS [t2] ON [t1].[field1] = [t2].[field1]

Jerry

"chris" <chris@discussions.microsoft.com> wrote in message
news:EF02710F-C208-47BF-94A7-7D692DC32CF7@microsoft.com...
> Hello:
>
> How do I write an sql statement to link tables in two databases and query
> them?
> For example:
>
> SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN
> db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
>
> This is for SQL Server 2000. Thank you.
>

Relevant Pages

  • Re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
    ... Sub Tester() ... Dim r As Recordset ... FROM tblBatches INNER JOIN (tblClusters INNER JOIN ((tblAdmin INNER ... you have a where statement that looks the same as the where statement in your SQL statement. ...
    (comp.databases.ms-access)
  • Re: Slow GROUP BY
    ... ' 'create primary key ... PunchList INNER JOIN Lot ON PunchList.LotId = Lot.Id INNER JOIN PunchItem ON ... the exact schema of the parent and child table ... your exact SQL statement that includes the GROUP BY ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Delete Query
    ... Susan ... >> A Delete Query needs to have the table specified. ... >> FROM ALMOST INNER JOIN 2_073105 ... >>> Below is the sql statement. ...
    (microsoft.public.access.queries)
  • Re: "Where" column limits records regardless of row
    ... inner join and none of the four fields used have any null values. ... GROUP BY., [qry SAME STORES IN IPT ... > If neither of those issues apply, post the SQL statement (SQL View on View ...
    (microsoft.public.access.queries)
  • SQL and recordset.Open (still having problems)
    ... SELECT ACCOUNT_LIST.COMPLEMENTOR FROM REP_LIST INNER JOIN ... I have the SQL statement variable declared as: ... >> strsearch variable. ... The error is telling you that the SQL command is ...
    (microsoft.public.access.modulesdaovba)