This is not as difficult as you might imagine. Let's say you have a username "bob". If Bob has authorization to BOTH dbs you can do:
select columns FROM dbName.Bob.tablename JOIN dbName2.Bob.tablename The connection informatoin actually stores the name of the DB you connect to - but its the username that locks you down to that DB. If you have permissions on other DBs, then you have access to them as well. Here's a snippet from books online: -------------------------------------------- The database in which the object resides may not always be the current database when the SELECT statement is executed. To ensure that the proper object is always used, regardless of the current database setting, qualify the object name with the database and owner: SELECT * FROM Northwind.dbo.Shippers --------------------------------------------- The big issue is whether or not the user has permissions on the database. If you can't do the above, query of a query might help you. You can do an access-like query that joins the 2 tables using Q of a Q after you have pulled the records from each. -Mark -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 12: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
