Query strategy

cf_dev2:

I didn't find anything in the MySQL manual regarding that as of yet. I'll use the other method and hope that this foxpro datasource will go away on its own...

Thank all for your help.

JFLaplante,

I don't know if you saw the previous comment. But some databases like MS SQL can communicate with other other db types. In MS SQL you can set up what's called a "linked server". A linked server can be queried almost as if it were another MS SQL database. Do you know if MySQL or Foxpro has a similar capability? Probably not, but it wouldn't hurt to check.

dan:

I had it working late yesterday with my fancy quotes! I just had to trim the column in the source query.

I had to trim them as well with the 'quotedvaluelist' function because they gave the exact same result. The code is alot nicer with 'quotedvaluelist' however .

I had hopped for a fully SQL solution but this does the trick nicely.

I would love to migrate those Foxpro tables to MySQL but I can't...

Thanks


'

You have the right idea but you have the quotes messed up. If t2query.id is text, use quotedvaluelist instead of value list. Or you can use cfqueryparam list = "yes".

dan:

I don't understand how I could use the valuelist function.

Are you sugesting something like this:

SELECT t1.* FROM t1 WHERE t1.id NOT IN ('#ValueList(t2query.id,"','")#')

where t2query is an already executed query that I don't want in the results.

* the fancy quotes is necessary to put all the values in quotes including the first and the last.

This will result in a very loooong query of a few 12' of lines containing all the excluded values. Somehow, the value list looks good but, several spaces are included inside each value and then they don't match their counterparts in the first table.

Also, this operation need to be done from a query because acessing the Foxpro database directly with Microsoft's ODBC driver will result in a "query too complex"

quote:

---

Originally posted by: JFLaplante
I need to do a query similar to this one:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);

The problem is that t1 is in a MySQL datasource and t2 is in a Foxpro datasource. I tried bringing everything I need under the for of queries and do a query of queries but it does necesitate a JOIN wich is not supported by QoQ.

I was able to do that in a QoQ like that;

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id

But I need the oposite and this:

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id<>t2.id

will obviously not work.

I 'could' copy t2 into a dummy table in the MySQL datasource each time and do everything from there but I would prefer a more efficient way.

Any ideas? I must be missing something easy...

Thanks

JF

---

The valuelist function is easy. Details are in the cfml reference manual. If you don't have one, the internet does.

I have done similar things in the past with MSSQL and Oracle by setting
up links in the database to another database. But only from one MSSQL
database to another MSSQL database and one Oracle database to another
Oracle database.

What I do not know is can one do something similar between dissimilar
database management systems.