CFQuery where clause comparison to query results

Question

I have a query that returns 7 records:

<cfquery name="equip" datasource="#datasource#">
select equipment_id from service_ticket
where equipment_id > 0
</cfquery>

I then take those results and use run another query again them:

<cfquery name="get_equipment_tally" datasource="#datasource#">
select *
from equipment_maintenance
where active = "yes" AND id <> #equip.equipment_id#
</cfquery>

But query "get_equipment_tally" is only comparing 'id' to the first record from my queryresults "equip" in the where clause, even though there are seven records returned.

Is there a way around that?

Answer 1

AND id NOT IN <cfqueryparam cfsqltype="cf_sql_integer" 
    value="#valueList(equip.equipment_id)#" list="true">

Ideally you would turn this into one query though

<cfquery name="get_equipment_tally" datasource="#datasource#">
select *
from equipment_maintenance
where active = "yes" AND id NOT IN (select equipment_id  from service_ticket where equipment_id > 0)
</cfquery>