Hello JoseLeon,

I have to add some more sql logic to the one you orginally wrote for
me to include data from another table for my CFLOOP results.

http://answers.google.com/answers/threadview?id=284711

This is the example table I orginally sent you.

     Product Search Table
===============================
|ID |    Word    | Date Added |
===============================
| 1 | CD Player  | 11/2/2003  |
| 2 | CVD Player | 11/2/2003  |
| 3 | Television | 11/2/2003  |
| 4 | VCR Remote | 11/3/2003  |
| 5 | Cable Box  | 11/2/2003  |
| 6 | Televison  | 11/2/2003  |
| 7 | Cable Box  | 11/7/2003  |
| 8 | Headphones | 11/7/2003  |
| 9 | Television | 11/12/2003 |
===============================


HERE IS YOUR ORGINAL QUERY:

=========================================================================
SELECT QueryTerm, count(QueryID) as QueryCount
FROM MasQueries
Group by QueryTerm
=========================================================================


HERE IS YOUR CF OUTPUT:


=========================================================================
<cfoutput query="a">
  <tr>
   <td valign="top" bgcolor="white">#CurrentRow#</td>
   <td valign="top" bgcolor="white">#QueryTerm#</td>
   <td valign="top" bgcolor="white">#QueryCount#</td>
  </tr>
</cfoutput>
=========================================================================

I have 2 other tables. The "supplier" table which holds each company
and a "supplier Keyword" table (which holds multiple keywords that
suppliers want to come back on when a user performs a search for a
product)


     Supplier Table    
===================================
|SuppplierID |  SupplierName      |
===================================
| 1          | ABC Computers      |
| 2          | Johns Stereos      |
| 3          | Fast Computer Inc. |
| 4          | BSA Electronics    |
===================================



  supplier keywordS Table
==========================
|SupplierID |   keywords |
==========================
| 1         | CD Player  |
| 2         | CVD Player |
| 2         | Remotes    | 
| 2         | VCR Remote | 
| 3         | Cable Box  |
| 4         | Televison  |
| 1         | Cable Box  | 
| 4         | Headphones | 
| 4         | Television |
| 1         | Blank CD   |
| 3         | Blank CD   |
| 3         | Television | 
| 3         | VCR Remote | 
| 1         | Cable Box  |
| 1         | Televison  |
| 1         | Cable Box  | 
| 2         | Blank CD   | 
| 2         | Television |
==========================

AS you can see from the "supplier Keywords" table above, it holds all
the keywords that each
supplier has entered that they want to come back on when the term is searched
on. 

Example:

For the keyword "Blank CD", suppliers 1,2,3 all entered this keyword
so they all come back when a user runs a search on this keyword. I
need to include the "Total amount of advertisers using each search
term" in the query you orginally wrote for me so it will look like
this:


=========================================================================
<cfloop query="a">
  <tr>
   <td valign="top" bgcolor="white">#CurrentRow#</td>
   <td valign="top" bgcolor="white">#QueryTerm#</td>
   <td valign="top" bgcolor="white">#QueryCount#</td>
   <td valign="top" bgcolor="white">#COUNT OF SUPPLIERS USING THIS KEYWORD#</td>
  </tr>
</cfoutput>
=========================================================================

FOr example, if looping through and the #QueryTerm# was "Blank CD" the 
#COUNT OF SUPPLIERS USING THIS KEYWORD# would be "3" because 3 suppliers are
using this keyword.

---

Request for Question Clarification by joseleon-ga on 15 Dec 2003 08:27 PST

Hello, belac:
  Thanks for this question, I start with it right now.

Regards

Hello, belac:

Here is the modified code, as you can see, I perform another query for
each QueryTerm, that way I get the count of suppliers that hold that
keyword:

<cfquery name="a" dataSource="LocalServer"
cachedWithin="#CreateTimeSpan(1,  4,  0,  0)#">
SELECT QueryTerm,  count(QueryID) as QueryCount
FROM MasQueries
Group by QueryTerm
</cfquery>	

	<cfoutput query="a">
		<tr>
			<td valign="top" bgcolor="white">#CurrentRow#</td>		
			<td valign="top" bgcolor="white">#QueryTerm#</td>
			<td valign="top" bgcolor="white">#QueryCount#</td>
			
		<cfquery name="b_query" dataSource="LocalServer">
		SELECT count(*) as keycount FROM SupplierKeywords where keywords='#QueryTerm#'
		</cfquery>	
		<td valign="top" bgcolor="white">#b_query.keycount#</td>		
		
		</tr>
	</cfoutput>

Be aware of the word wrapping when copying and pasting and don't
hesitate to request for any clarification.

Regards.

---

Request for Answer Clarification by belac-ga on 16 Dec 2003 16:14 PST

Sorry for the delay, have a family health issue. 

Thanks Jose, it worked. Clarification: Could I also add the names of
the suppliers using the keyword next to the count? Would I inner join
the suppliers table on the "b_query"  so I could get the suppliers
names next to the total count?

---

Clarification of Answer by joseleon-ga on 17 Dec 2003 06:46 PST

Hello, belac:
 This is the final code, note also the "distinct" keyword has been
added to the count query, this way you will get only the real results
without duplicates.

	<cfoutput query="a">
		<tr>
			<td valign="top" bgcolor="white">#CurrentRow#</td>		
			<td valign="top" bgcolor="white">#QueryTerm#</td>
			<td valign="top" bgcolor="white">#QueryCount#</td>
			
		<cfquery name="b_query" dataSource="LocalServer">
		SELECT count(distinct SupplierID) as keycount FROM SupplierKeywords
where keywords='#QueryTerm#'
		</cfquery>	
		<td valign="top" bgcolor="white">#b_query.keycount#</td>		
		
		<cfquery name="c_query" dataSource="LocalServer">
			SELECT     distinct a.SupplierID, a.SupplierName
			FROM         Suppliers a INNER JOIN
                      	SupplierKeywords b ON a.SupplierID =
b.SupplierID AND b.keywords = '#QueryTerm#'
		</cfquery>		
		<td valign="top" bgcolor="white">
		<cfloop query="c_query">
		#c_query.SupplierName#<br>
		</cfloop>	
		</td>
		
		</tr>
	</cfoutput>

 Just tell me if you need anything else.

Regards.

Excellent, fast and responsive. Thanks again.