Topic: Joining


riogrande    -- 02-26-2008 @ 11:26 PM
  I am trying to the set the value countries.country when clientinfo.countryid = countries.countryid instead of the countryname in the q_countries query. Thne Countryid field not the countries.country field is being output in the cfchart. The join should make it work but I have been unsuccessful in my attempts to make it work as it is outputting countryname as countryid not country (although I donĀ“t get any error)
Where am I going wrong? TIA

Here is the result at present:
Query result- Rows: 3

COUNTRYNAME VISIT_COUNT

1 LESS THAN 5 VISITS 4
2 NOT 12
3 US 5


<!--- GROUP COUNTRIES DEFINTION DYNAMIC --->
<cfquery name="country" datasource="mydts">
SELECT CASE WHEN Visit_Count >= 5 THEN countryid ELSE 'LESS THAN #NEWVALUE# VISITS' END AS
CountryName, SUM(Visit_Count) AS Visit_Count
FROM (
SELECT UCASE(countries.Countryid) AS Countryid, COUNT(*) AS Visit_Count
FROM ClientInfo
JOIN countries ON clientinfo.countryid = countries.countryid
WHERE month(clientinfo.visit) = #url.monthnowis#
and day(clientinfo.visit) = #url.daynowis#
GROUP BY UCASE(Country)
) AS x
GROUP BY CountryName
</cfquery>

Here is the cfdump result at present as you can see the countryname should be countries.country when clientinfo.countryid=countries.countryid:

Country query - Rows: 3

  COUNTRYNAME         VISIT_COUNT

1 LESS THAN 5 VISITS      4  
2 NOT                    12  
3 US                      5  


This message was edited by riogrande on 2-27-08 @ 10:31 AM


EasyCFM.COM ColdFusion Forums : http://archive.easycfm.com/forums
Topic: http://archive.easycfm.com/forums/viewmessages.cfm?Forum=32&Topic=13188