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
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
