Axelology
Posted At : November 16, 2006 1:03 PM | Posted By : Axel Jensen

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>

Related Categories: SQL
Comments (8) | Print | Send | del.icio.us

Related Blog Entries

Comments
[Add Comment]
Divx Full Movies Download
http://choosedvdmovies.com
# Posted By Divx Full Movies Download | 6/22/08 2:49 PM
Um ein gutes Briefpapier Design zu erhalten, sollte man die DIN Norm 676 für Briefbogen Layout beachten und um einen Brief für Geschäftsbriefbogen richtig zu erstellen, sollte man die DIN Norm 5008 beachten. Mehr Informationen zu Briefbogen Design unter:
http://www.trendstrom.de/briefbogen_gestalten_druc...
# Posted By Designer | 6/25/08 9:24 AM
good article.
http://www.estetik-merkezleri.com estetik
http://www.otoarackiralama.com oto kiralama
http://www.sacekimmerkezi.org saç ekimi
# Posted By estetik | 7/4/08 5:00 AM
Thank you very good
<a href="http://www.teomandogan.com">Estetik</a>
http://www.teomandogan.com
http://www.evkur.org
http://www.bayanlardernegi.com
http://www.teomandogan.com/burun_estetigi.htm" target="_blank">http://www.teomandogan.com/burun_estetigi.htm
http://www.teomandogan.com/meme_kucultme_ameliyati.htm" target="_blank">http://www.teomandogan.com/meme_kucultme_ameliyati...
# Posted By plastik cerrah | 7/20/08 2:15 PM
[Add Comment]



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.