complex query problem

[lucidtech]

I'm trying to run a complex query using the cfquery tag (query of queries won't work here as I want to use LIMIT and query of queries will not allow it)

I want to query a database where the WHERE clause is whether another query has a record count. Basically query a table where, in another table, there is a record in the second table that matches the id in the first table. Here's an example (not working) of what I'm trying to do.

<cfquery name="result">
SELECT *
FROM invtypes
WHERE (SELECT effectID from dgmeffects where typeID = invtypes.typeID AND effectID = 12) > 0
</cfquery>

 

[cfSearching]

If you are simply testing for the existence of one or more matching records, use EXISTS

-- not tested
SELECT i.*
FROM invtypes i
WHERE EXISTS
(
SELECT d.effectID
FROM dgmeffects d
WHERE d.typeID = i.typeID
AND d.effectID = 12
)




----------------------------------

http://cfsearching.blogspot.com/

 

[lucidtech]

Worked great, thanks