Classic ASP Discussion - Selecting from two database problem

Print Page | Close Window

Selecting from two database problem

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=14316
Printed Date: 30 March 2026 at 10:07am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Selecting from two database problem

Posted By: srb01uk
Subject: Selecting from two database problem
Date Posted: 19 March 2005 at 10:00am

I am attemting to select data from three tables, one of the tables lives in a different database on the same server. I have included an example of the select statement, but I think the issue revolves around the database.owner.object_name. It maybe to do with correct sytax but could not find any examples only to compare.

I understand that data can be selected from other databases even though the strSQL opens only one database connection?

Does anyone have any suggestions please?

Thanks in advance.

ulive.SB.admissions (ulive is database name, SB is username, admissions is table name)

strSQL = "SELECT capd_person.p_forenames, capd_person.p_surname, capd_person.p_dob, apd_student.s_studentreference, capd_student.s_studentstatus, admissions.arrival_status " & _
"FROM capd_person INNER JOIN capd_student ON capd_person.p_id = capd_student.s_id LEFT JOIN ulive.SB.admissions ON capd_student.s_studentreference = admissions.stu_reference " & _
"WHERE admissions.arrival_status = '" & arrival_status & "' "

Replies:

Posted By: michael
Date Posted: 19 March 2005 at 6:09pm

SQL Server uses a dot-delimited nomenclature that extends outward to the database and the server. To specify a column in a table in another database, name the other database:

SELECT CustomerID FROM Common.Customers

To join a table from the Common database to a table in the current (Sales) database, use this same nomenclature:

SELECT * FROM Common.Customers
INNER JOIN Orders
ON Common.Customers.CustomerID = Orders.CustomerID

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker