EasyCFM.COM ColdFusion Forums / MySQL Related Issues / Joining

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Joining

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

riogrande
02-26-2008 @ 11:26 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

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


Website Designed and Developed by Pablo Varando.