Using SQL sub select statments, or selects within select statments

In our team of developer’s we use a tracking system, to track work orders, bug fixes, feature requests, and the status of those projects…

One of my recent assignments was to create a “Recently Updated List”

This list will contain the trackID, and a Title… it will sort by the date in descending order so we always see the most recent items first… It will join 2 tables – items(v_items), and events…

Items is the Main table, and Events are details about that item that happen along the tracking of that item…

For example, say you have track 897 897

========

(below are events)

—————————————–

edited by axel on 11/11/06 @ 2:00pm

We now are finished with this track

—————————————–

—————————————–

edited by axel on 11/11/06 @ 1:30pm

We did something else

—————————————–

—————————————–

edited by axel on 11/11/06 @ 1:00pm

we edited the table in the database to meet a need

—————————————–

<cfquery datasource=“#Application.AdminDsn#” name=“getFeaturesList”>

SELECT

v.itemid, v.title,

e.datecreated, e.eventid

FROM v_items v

INNER Join Events e

ON v.itemid = e.itemid

WHERE

e.eventid in (

SELECT TOP 1 e2.eventid

FROM events e2

WHERE e2.itemid = v.itemid

AND e.createdbyuserid = 92

ORDER BY e2.datecreated desc

)

AND

e.datecreated >

<cfqueryparam cfsqltype=“cf_sql_date”

value=“#DateAdd(‘m’,-1,Now())#”/>

AND v.statusID IN (5)

ORDER BY e.datecreated desc

</cfquery>

Leave a comment