SQL Query Help

[FSUKXAZ]

I have a query in my CF application I'm trying to rewrite because I just don't like it.  Currently we have a query grabbing all the Locations based on a certain time frame (2 weeks) so if there has been an article released in the past 2 weeks it grabs that location. That result, of locations, is put into a list using ValueList function.  Then that list is looped over, and inside that loop is a new query that grabs all the results. Then that is output to the screen with the articles grouped by location.  The query works, but the problem is it fires like 20 times per all the locations (plus or minus)... and then there is the original query.  It's just too many hits to the database.

I would like one query to accomplish everything.  Everything = all articles over the last 2 weeks, no locations that don't have articles over that time frame, and group results by location. I've tried Subqueries, GROUP BY, and HAVING and cannot get it to work. Argh!  I know I'm missing something, but the light bulb hasn't turned on yet. 

We'll call the table "Articles" and that table contains the fields "ID", "Title", "Summary", "Location", "Link", and "ArticleDate".

[Joshua_Cyr · Accepted solution]

So just one table right?  Did you know you can nest cfoutput with a group option?

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_m-o_16.html

select  ID, Title, Summary, Location, Link, ArticleDate

From

Article

Where ArticleDate between (date span stuff here)

AND location IS NOT NULL

Order By Location

<cfoutput query="queryname" group="location>

<h2>#location#</h2>

  <cfoutput>

  #title#<br>

  </cfoutput>

</cfoutput>

[FSUKXAZ]

Yes, it's one table.  And yes, I know you can group by inside the cfoutput.  In fact i did that on another CF page.  However, I was missing something additional in the WHERE clause for this page.  I would never have thought to use what you did "AND location IS NOT NULL", since the Location is always populated and is never NULL.  It doesn't compute in my head, but what matters is that it does on the computer!   That worked!   Thank you for your support.

[-__cfSearching__-]

Location is always populated and is never NULL. 

It can also apply when using outer joins. A column's value might be considered NULL in the context of a join.

[Dan_Bracuk]

If you are looping through a value list and running queries containing something like:

where somefield = #the list item#

You can usually replace it with a single query containing

where somefield in (#the list#)

Having said that, this is still two queries.  If they are from the same datasource, with a bit more thought you should be able to reduce that to one.