I believe you can reference the table using the database as well.
For example:
SELECT *
FROM datasource1.dbo.anytable dt1 INNER JOIN
datasource2.dbo.anytable dt2 ON dt1.record_id = dt2.record_id
This may be ODBC specific though.
Jeff
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 06, 2003 1:26 PM
To: CF-Talk
Subject: SQL: Brain Teaser2
I have a central login system The user/login information is stored in a
datasource called "CENTRAL_LOGIN".
I then have multiple "stand alone" applications which have their own
datasources... for example:
DATASOURCE1
DATASOURCE2
DATASOURCE3
Etc...
The central login system works fine. But one of the problems I'm
experiencing is that sometimes I'd like to do an SQL join on tables which
exist in multiple datasources. For example, Let's say that "DATASOURCE3"
contains a table which stores the USER_ID of the person who added a the
record to that table.
CENTRAL_LOGIN -- TABLE1
-----------------------------------
USER_ID,USERNAME,PASSWORD
1,john,apple
2,sam,orange
3,betty,password837!
DATASOURCE3 -- TABLE1
-------------------------------------------
USER_ID,COMMENT
1,This is a test.
3,This is another test.
2,This is a test again.
Etc...
I want my output to look as follows:
USERNAME,COMMENT
john,This is a test.
betty,This is another test.
sam,This is a test again.
This would be simple if all tables were in the datasource. I could just do
a regular join and get the username. But in this case... the tables are in
two different datasources. I'm using SQL 2000.
Merging all of the datasources together really isn't an option. It makes me
wonder how central login systems are SUPPOSED to be designed. How does
Microsoft Passport work for example? Is there a way to do a join across
multiple datasources in SQL 2000?
-Novak
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
