Query that only returns items that will produce a result

[Lee Crockett]

Thanks to Mack for his help yesterday.  I would really appreciate some help from anyone who is more SQL competent than I am.  I have an SQL problem that is just completely over my head.  I've created a nifty tagging system for the blog, that sorts by tags and by multiple tags, check out the beta here: http://committedsardine.com/blog.cfm

When a user selects a tag, it adds it to the value list SESSION.blogTags.  If the selected tag is there already, it removes it.  When the list for tags pops up, I output all the tags, and show their state.  You'll see what I mean if you try it.

What this leads to is the ability to select a group of tags for which there are no query results.  What I want to do is only show those that will generate results and how many results they'll show.  Like this, select "fluency" by itself there are 310 entries

fluency (310) | digital (234) | writing (12)

Once fluency is selected, there are 13 articles that ALSO are tagged by "digital", but none that are tagged by writing:

fluency | digital (12) | writing

I have a table called blogTagLinks, that is just for tying a tag to a blog.  It lists a blogID and a tagID.  Here is a sample of it for reference:

blogTagLinkID	blogID	tagID
4	2	2
5	2	3
6	2	5
39	1	18
49	1	1
42	1	9
44	1	19
47	5	14
48	1	22
54	16	22

I'm including all my sql, but the spot that I need help with is marked in red below:

<!---if URL.tg is defined, check to see if it exists in the database, then the SESSION, and either add or delete it from SESSION--->

<cfquery name="rsAllTags" datasource="">

SELECT tagsID, tagName

        FROM tags

        WHERE tagActive = 'y'

</cfquery>

<cfset allTags = ValueList(rsAllTags.tagsID)>

<cfif isDefined("URL.blogTags")>

    <cfif ListFind(allTags, URL.blogTags) NEQ 0>

        <cfif ListFind(SESSION.blogTags, URL.blogTags) NEQ 0>

            <cfset SESSION.blogTags = ListDeleteAt(SESSION.blogTags, ListFind(SESSION.blogTags, URL.blogTags))>

            <cfelse>

            <cfset SESSION.blogTags = ListAppend(SESSION.blogTags, URL.blogTags)>

        </cfif>

    </cfif>

</cfif>

<!---get a list of all available tags, tags that if added to the already selected tags, will return a result--->

<cfquery name="rsAvailableTags" datasource="">

SELECT tagsID, tagName

        FROM tags

        WHERE tagActive = 'y'

        NEED SOME STATEMENT HERE OF BLOGTAGLINKS TO DETERMINE WHAT TAGS WILL PRODUCE A RESULT

</cfquery>

<!---if searching by tags, get a list of the currently selected tags for display, the 0 returns an empty result if there are no tags--->

<cfif isDefined("SESSION.sb") AND SESSION.sb EQ "tg">

    <cfquery name="rsTags" datasource="">

        SELECT tags.tagName, tagsID

        FROM tags

        WHERE tagsID <cfif SESSION.blogTags NEQ "">IN(#SESSION.blogTags#)

        <cfelse> = 0</cfif>

    </cfquery>

    <cfset variables.newrow = false>

</cfif>

<!---get the information for the blogs list, filtered by keyword or tag if requested--->

<cfquery name="rsBlog" datasource="">

    SELECT blog.blogID,

        blog.storyID,

        blog.blogDate,

        blogStories.storyID,

        blogStories.blogTitle,

        SUBSTRING(blogStories.blogBody,1,200) AS blogBody,

        images.imageName

    FROM blog, blogStories, images

    WHERE blog.storyID = blogStories.storyID AND images.imageID = blog.photoID AND blog.blogDate < "#todayDate#" AND blog.deleted = 'n'

<cfif SESSION.sb EQ "kw">AND  CONCAT(blogStories.blogBody, blogStories.blogTitle) LIKE '%#SESSION.blogKeywords#%'</cfif>

    <cfif SESSION.sb EQ "tg" AND SESSION.blogTags NEQ "">

            AND  blog.blogID IN (

            SELECT blogID

            FROM blogTagLink

            <cfif SESSION.blogTags NEQ "">

                WHERE tagID IN(<cfqueryparam cfsqltype="cf_sql_integer" value="#SESSION.blogTags#" list="true">)

                GROUP BY blogID

                HAVING count(tagID) = #ListLen( SESSION.blogTags )#)

            </cfif>

     </cfif>

ORDER BY blog.blogDate DESC

</cfquery>

[mack_]

There might be a single query solution but here's a query that you

will need to run for each tag in the database (cfloop over all the

tags) and will give you the number of blogs that have the selected

tags + the current tag

SELECT Count(*) AS blog_count
FROM (
    SELECT blogID
    FROM blogTagLink
    WHERE tagID IN(<cfqueryparam cfsqltype="cf_sql_integer"
value="#SESSION.blogTags#" list="true">)
    	OR tagID = #currentTagID#
    GROUP BY blogID
    HAVING count(tagID) = #ListLen( SESSION.blogTags )#
    	OR count(tagID) = #ListLen( SESSION.blogTags )# + 1
    ) AS blogs

Mack

[Lee Crockett]

Thanks Mack:

I've never used a cfloop before, and after reading up on it, and spending a whole bunch of time trying to figure it out.  I have to admit defeat and say I have no idea how to do this.

[mack_]

<cfloop query="AllTags">
  <cfquery datasource="#ds#">
    __place here the query above, where currentTagID is the tag ID
from allTags__
  </cfquery>
</cfloop>

Mack