Jason,
Try this (untested):
<cfquery name="myquery" datasource="data1">
SELECT tbl_1.id as id, tbl_1.member_id as member_id, tbl_1.info_a as
info_a, tbl_2.info_b as info_b
FROM dbo.tbl_1
INNER JOIN mydatabase2.dbo.tbl_2 ON tbl_1.member_id = tbl_2.member_id
WHERE (tbl_1.member_id =<cfqueryparam value="#member_id#">)
</cfquery>
Replace mydatabase2 with the name of your second database. Also,
whenever I work with MS SQL, I always preface my table names with "dbo."
to guarantee I'm pulling from the correct schema (some of my databases
have schemas other than dbo). It makes it easier to remember to just
add the database name ahead of that when cross-linking databases.
Carl
On 11/10/2010 12:23 PM, Jason King wrote:
Right now.. the databases on on the same server, same instance. Does
anybody have any example syntax? I'm working on it now but I've never
had to do a join like this so I'm still trying to figure out who to
lay it all out.
On Wed, Nov 10, 2010 at 2:06 PM, Matthew Woodward
<[email protected] <mailto:[email protected]>> wrote:
On Wed, Nov 10, 2010 at 12:02 PM, Carl Von Stetten
<[email protected] <mailto:[email protected]>> wrote:
+1 on the link Matthew sent. If the databases are on two
different servers (or two different instances of SQL Server on
the same box), you'll need to create a linked server on one of
the servers to connect to the other one.
Right--good point, and bear in mind this is something I haven't
had to do in about 5 years. But you don't create datasources to
both databases. You just point to one, and on that server you do
whatever is necessary to let you refer to the other database
within your SQL. At least that's my recollection.
--
Matthew Woodward
[email protected] <mailto:[email protected]>
http://blog.mattwoodward.com
identi.ca <http://identi.ca> / Twitter: @mpwoodward
Please do not send me proprietary file formats such as Word,
PowerPoint, etc. as attachments.
http://www.gnu.org/philosophy/no-word-attachments.html
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
official manual: http://www.openbluedragon.org/manual/
Ready2Run CFML http://www.openbluedragon.org/openbdjam/
mailing list - http://groups.google.com/group/openbd?hl=en
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
official manual: http://www.openbluedragon.org/manual/
Ready2Run CFML http://www.openbluedragon.org/openbdjam/
mailing list - http://groups.google.com/group/openbd?hl=en
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
official manual: http://www.openbluedragon.org/manual/
Ready2Run CFML http://www.openbluedragon.org/openbdjam/
mailing list - http://groups.google.com/group/openbd?hl=en
