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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
