Query of Queries

Thanks Dan

I figured it out..... Awesome.... It takes about 3 secs. to load the page.
123 was a bad choice for a query name example.

Thanks for your help

Oh... I re read the first post and missed the part you say:
"the where clause will use that valuelist function I alluded to" on the second query.

So basically I'll create a list from the first query (the small one) to narrow the records to be searched on the big table.

<cfquery name='abc' datasource='1'>
select id_field
from the_table
<cfquery>
<cfset mylist = #ValueList( abc.id_field, ", " )#>

<cfquery name='123' datasource='2'>
select id_field
from the_table
where abc.id_field in (#mylist #)
<cfquery>


But I'm confused on how to use the QoQ

Would you mind to point me in the right direction here?

Thanks in advance

No, not like that.

There is no need to bring 970,000 records into Cold Fusion. That much data will slow you down to a crawl.

Plus your syntax for query of queries is wrong. The query itself is considered to be a table.

Like this??

<cfquery name='abc' datasource='1'>
select id_field
from the_table
<cfquery>

<cfquery name='123' datasource='2'>
select id_field
from the_table
<cfquery>
<cfset mylist = #ValueList( id_field, ", " )#>

<cfquery name='getrecords' dbtype='query'>
select abc.id_field
from abc.the_table
where abc.id_field in (#mylist #)
<cfquery>

My approach would be

Query 1 from datasource with the small table
select id_field
from the_table

Query 2 from datasource with the big table
select id_field
from the_table
the where clause will use that valuelist function I alluded to

Query 3 q of q
select id_field from query 1
and here is that valuelist again

Yep I don't have control over the datasource.... I guess I'll have to setle for the QoQ.

I did create it but the amount of time it takes is ridiculous

tableA and tableB are in different datasource

That is my poblemo

I think the solution I'd go with is creating a view with an inner join from the two tables. Then, whenever you want to validate the data update all the rows in the smaller table which aren't found in the view, and to copy those items in the view to a new table (if indeed you need another table besides the view).

With the information provided so far, it's a bad idea all around. Simple sql will tell you what records from Table A are or are not in Table B. Simple sql will continue to give you the correct answer whenever you add a record to Table A, whether it's in Table B or not.

Query of Queries is not the best solution. The best solution is to not touch your data at all.