Joining records from 2 datasources

[chexonec]

Can it be done. I am getting userIDs from one database and I need to show the
records those userIDs correspond to from the other database. The DBs are on
differnet datasources.

Right now I am creating a list of userIDs and doing this:

SELECT * FROM database1..table1
WHERE userID in (#userIDlist#)

I would rather do:
SELECT * FROM database1..table1
WHERE userID in (SELECT userID FROM database2..table2)

Thx for any help.

[mxstu]

What database(s) are you using? Are the databases on the same server?

[zoeski80]

Sorry, each query within CFQUERY tags can only be performed on a single
datasource.

The way you're doing it is probably the best solution for a small list. BUT if
you are talking about a long list of ID values Oracle will have a problem when
it gets to 1000 and the speed isn't too great on long lists for any database.
You could try using QoQ which will look like a silly method, but will work.

Query on DS1 named DS1query

Query on DS2 named DS2query

SELET *
FROM DS1query
WHERE ID IN (SELECT ID FROM DS2query)


Another alternative is to write a Stored Procedure for it as they can access
data from other databases, the Stored Procedure can then be called from CF.

HTH

Zoe

[mxstu]

Most enterprise level databases allow you to reference other databases or link
to other database servers. With the correct permissions, you should then be
able reference both database objects in the same cfquery.

[mxstu]

Most enterprise level databases allow you to reference other databases or link
to other database servers. With the correct permissions, you should then be
able reference both database objects in the same cfquery.

[CF_Oracle]

mxtu is right. At least in Oracle we use 2 sources when needed.
For example, if you (your DBA) granted SELECT permission for table1 in
database1 for users of database2 your query could look like this (supposed your
DSN provides connection to database2):
SELECT * FROM database1.table1
WHERE userID in (SELECT userID FROM table2).
OR
If you granted SELECT permission for table2 in database2 for users of
database1 your query will look (supposed your DSN provides connection to
database1):
SELECT * FROM table1
WHERE userID in (SELECT userID FROM database2.table2).

[jorgepino]

did you try to use a inner join?

[dj shane]

Two options. I've accomplished it either way, just depends on the situation.

1. Use a query of queries. Query one, select your data from datasource 1. Then
select your data from datasource 2. Use a query of query for the 3rd query and
join with the result set from both. Could use queryaddColumn() within this.

2. Simpler approach. Setup a scheduled task to dump the data from your
datasource into the other datasource nightly. This depends a lot on your
situation. I've done this when it makes sense. I recently had a instance where
I was generating some substantial reports and doing the nightly task vs the
joining on another datasource just was much more efficient, as processing time
needed to be optimal.

Hope this helps,
Shane

[mxstu]

dj shane,

Why pull all the information across or use separate queries? Doesn't your database support referencing other databases/linking servers?