I have used the database but did not know you can use the server also. I have found that the database attribute specified in the cfquery tag can be either of the databases and it works fine since it is specified in the sql string.
Dan -----Original Message----- From: Bill Grover [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 2:05 PM To: CF-Talk Subject: RE: Brain Teaser2 I forgot to add you can even expand it to include different servers. You need to create a linked server but once you do you can access it remotely. So the expanded version would be: SELECT * FROM server.database1.dbo.anytable dt1 INNER JOIN server.database2.dbo.anytable dt2 ON dt1.record_id = dt2.record_id ______________________________________________________ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299 WWW: http://www.euservices.com ______________________________________________________ > -----Original Message----- > From: Bill Grover > Sent: Wednesday, August 06, 2003 1:54 PM > To: CF-Talk > Subject: RE: Brain Teaser2 > > > That's pretty much how you do it in MS SQL (SQL7, SQL2000). > The difference is that you use the database name, which may > or may not be the same as your datasource name. So your > example is more like: > > SELECT * > FROM database1.dbo.anytable dt1 INNER JOIN > database2.dbo.anytable dt2 ON dt1.record_id = dt2.record_id > > ______________________________________________________ > > Bill Grover > Supervisor MIS Phone: 301.424.3300 x3324 > EU Services, Inc. FAX: 301.424.3696 > 649 North Horners Lane E-Mail: [EMAIL PROTECTED] > Rockville, MD 20850-1299 WWW: http://www.euservices.com > ______________________________________________________ > > > > > -----Original Message----- > > From: Sarsoun, Jeff [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, August 06, 2003 1:44 PM > > To: CF-Talk > > Subject: RE: Brain Teaser2 > > > > > > 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
