Common from 4 query results

[balumohan]

Hi All,

Is there any easy way to get the common records from a dynamic number of query results. I mean I want to get like intersect in sql... the query results will be stored like recordset1,recordset2,recordset3.....

Please help!!!

[Dan_Bracuk]

I've never tried an intersect query of queries, but this might work

<cfquery dbtype = "query">

<cfloop list = "#querylist# index = "thequery">

select id from #thequery#

intersect

</cfloop>

select id from #ListFirst(querylist)#

[balumohan]

Hi Dan,

I think we cant use INTERSECT for QoQ. Is there any other alternative??

[Dan_Bracuk]

<cfquery dbtype = "query">

select id from #querylist#

where 1 = 1

<cfloop from = "1" to = "#listlen(querylist)# - 1" index = "i">

and #listgetat(querylist, i)#.id = #listgetat(querylist, i + 1)#.id

</cfloop>

[balumohan]

Hi,

But this will work only if querylist have only two results sets maximum. Is there any other way if there are more than two??

[Dan_Bracuk]

what happened when you tried it with more than 2 queries?