In fact, as long as the user you use in the cfquery tag has access to the databases none of the databases referenced in the select statement have to match the database in the cfquery tag. ______________________________________________________
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: Dan O'Keefe [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 06, 2003 2:29 PM > To: CF-Talk > Subject: RE: Brain Teaser2 > > > 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
