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?

Your observation of `is only comparing 'id' to the first record ` is the default behaviour when you don't specify a row number. — Dan Bracuk, Mar 10 '17 at 18:40

score 2 · Accepted Answer · answered Mar 10 '17 at 18:19

2

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>

answered Mar 10 '17 at 18:19

Matt Busche

14,216
5
36
61

That worked perfect, thanks! I had no idea you specify a query like that in the where clause. – Brian Fleishman Mar 10 '17 at 18:30
1

SQL has a ton of flexibility, when in doubt try it out and see what happens. – Matt Busche Mar 10 '17 at 18:40
@BrianFleishman - FWIW, it is called a [subquery](https://dev.mysql.com/doc/refman/5.7/en/subqueries.html). You could also achieve the same result with [EXISTS](https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html). There are [pros and cons to each](http://stackoverflow.com/questions/25756112/mysql-exists-vs-in-correlated-subquery-vs-subquery) depending on the exact query, indexes, etcetera. ([EXPLAIN](https://dev.mysql.com/doc/refman/5.7/en/explain.html) is handy if making comparisons) – Leigh Mar 10 '17 at 19:06
Awesome. Thanks. – Brian Fleishman Mar 10 '17 at 21:54

score 0 · Answer 2 · answered Apr 06 '17 at 02:26

I like these answers, but seeing NOT IN in a query gives me twitches. Use a LEFT OUTER JOIN... IS NULL instead. It will be MUCH faster.

SELECT <list of cols here>
FROM equipment_maintenance em
LEFT OUTER JOIN equipment_id ei ON ei.equipment_id = em.equipment_id
WHERE em.Active = 'yes'
 AND ei.equipment_id IS NULL

Something like that.

CFQuery where clause comparison to query results

2 Answers2