Seth,

On Saturday, February 21, 2004, 4:17:42 PM, you wrote:

SK> One thing that may be causing it is one datasource is in SQL and
SK> the other is in access.

I don't think it should matter that they're different DBMSs. I would
guess that QoQ just can't do this type of query. Also make sure your
version of CF supports QoQ (I think it starts at CF5).

SK> <cfquery datasource="ds1" username="u" password="p" name="main">
SK>       SELECT common.id, common.full_name
SK>          FROM csusa_common
SK> </cfquery>

SK> <CFQUERY datasource="ds2" name="mine">
SK>          SELECT filename, id FROM table1 ORDER BY id
SK> </CFQUERY>

SK> <cfquery dbtype="ds3" name="final">
SK>           select common.id, common.full_name, id, filename
SK>           from main, mine
SK>           where main.csusa_common.id = mine.id
SK> </cfquery>

When you use a QoQ, the original query results are treated as if they
are tables, so I believe it's incorrect to reference the original
table names in your QoQ. All the QoQ needs to know is the source query
name (used as a table name) and the column(s).

Also, if I understand your needs here, the use of UNION in the QoQ is
not what you need (I don't recall if it's even supported) as each
table has a common ID that you are trying to use to match a given user
to their file(s).

A UNION requires that both source tables/queries feature the exact
same column names (I believe you can use aliases to make sure they
match):

select id, name from foobar
union
select id, name from foobar2

Say foobar has the following records:

1       Bob
3       Jane

And foobar2 has:

3       Gordon
44      Elizabeth

A UNION would return:

1       Bob
3       Jane
3       Gordon
44      Elizabeth

If the join I mentioned earlier doesn't work in QoQ, perhaps you can
query the remote data, dump it into a local table, and then do a
standard (i.e., non-QoQ) SQL query joining your original local data
with the data you just imported.

~ Ubqtous ~
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

Reply via email to