?
Solved

CF8 CFQUERY -  help on query of two tables then cfoutput of cfquery

Posted on 2012-09-07
15
Medium Priority
?
641 Views
Last Modified: 2012-09-26
I have a members table and a photo table. Common key Member Id.  Not all members have a Photo table entry - only those who have posted photos.

I want a query that will read both tables and then when I cfoutput the query if  I have an entry on the photo table I go out and get the corrresponding photo in the link (named with the member Id) If not photo I get the default placeholder image. Can have up to 14 photos but I check for slidenum = 1 : the profile photo and use that.

I then cfoutput the query and format the report - either with a photo or a placeholder default pic

MembersTable
MemberID
Country
City
Username
Name
etc

PhotoTable
MemberId
SlideNum


Can someone please give me query and cfoutput of this.
So eg the link for the member with photo could be
www.mysite.com/photos/18379493.jpg
and the link for member without photo would be
www.mysite.com/pics/placeholders/defaultimage.jpg


eg select from Members where country = "New Zealand"
and City = "Auckland"

etc


Thanks for your help
0
Comment
Question by:Ian White
  • 9
  • 6
15 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38379815
You need an OUTER join.  This would give you all members in the city "Auckland". If they don't have a SlideNum = 1, the photo name will be empty.

               SELECT m.MemberID
                          , m.Name AS MemberName
                          ,  p.TheColumnWithThePhotoFileName
               FROM    MembersTable m LEFT JOIN PhotoTable p
                            ON    p.memberID = m.memberID
                            AND  p.SlideNum = 1
              WHERE  m.country = "New Zealand"
              AND       m.City = "Auckland"


Output the query and use the default photo when "TheColumnWithThePhotoFileName" is empty:

        <cfoutput query="yourQueryName">
                 Member Name:  #Name# Link:  

                <cfif len(trim(TheColumnWithThePhotoFileName))>
                     1ST SLIDE: /photos/#TheColumnWithThePhotoFileName#
                 <cfelse>
                     USE DEFAULT: /pics/placeholders/defaultimage.jpg
                 </cfif>
                 <br>

        </cfoutput>
0
 

Author Comment

by:Ian White
ID: 38381496
HI Sorry this wont work.

I want to get all members that meet the location criteria (on members table eg Country = New Zealand City = Aucklkand)  Many of these members wont have a record (MemberID) in the PhotoTable.  You see I need the lot as I use pagintation to output - could be 1,000 members and only 400 may have a record in the photoTable.  So this join would only get members with p.slidenum= 1

I dont store the columwiththePhotoTable  . If they have a photo they have a record in the PhotoTable with key their memberid  The photos file is made up of Their memberid +  slidenumber + jpg

Appreciate your help in getting there. Thanks
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 38381620
> So this join would only get members with p.slidenum= 1

Nope :) That's the beauty of a LEFT join. It returns all records from the left hand table (ie members) even when there's no matching record in the other table. In other words, you'll get all members from Auckland, even if they don't have slidenum = 1 photo. Go ahead and try it. Barring any typos it should do exactly what you need.

       > The photos file is made up of Their memberid +  slidenumber + jpg

Ok, you could either concatenate the values in the SQL -or- in the CF code. Using CF is the simplest:

             <!--- use cfqueryparam in the real query --->
             <cfquery name="yourQueryName" ....>
              SELECT m.MemberID
                          , m.Name AS MemberName
                          ,  p.SlideNum
              FROM    MembersTable m LEFT JOIN PhotoTable p
                            ON    p.memberID = m.memberID
                            AND  p.SlideNum = 1
              WHERE  m.country = "New Zealand"
              AND       m.City = "Auckland"
           </cfquery>

     <cfoutput query="yourQueryName">
                 Member Name:  #Name# Link:  

                <!--- the member has a photo ...--->
                <cfif SlideNum eq 1>
                     1ST SLIDE: /photos/#MemberID##SlideNum#.jpg
                 <cfelse>
                     USE DEFAULT: /pics/placeholders/defaultimage.jpg
                 </cfif>
                 <br>

        </cfoutput>
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:Ian White
ID: 38385522
a
0
 

Author Comment

by:Ian White
ID: 38385732
Thanks that worked


BTW Do you know if CFLOOP within CFOUTPUT of query results can get unpredictable results - as that is what is happening


Also if I wanted to get a count on the number of Photo records during the first query - how can I do that?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38386213
If you want to include a COUNT, you need a different query.

  SELECT m.MemberID
              , m.Name AS MemberName
              , MAX( CASE WHEN p.SlideNum = 1 THEN 1 ELSE 0 END ) AS FirstSlideNum
              , COUNT(p.SlideNum) NumOfSlides
  FROM    MembersTable m LEFT JOIN PhotoTable p
                   ON    p.memberID = m.memberID
  <!--- cfqueryparam omitted for brevity --->
  WHERE  m.country = "New Zealand"
  AND       m.City = "Auckland"
  GROUP BY m.memberID, m.Name

...
        <!--- the member has a photo ...--->
        <cfif FirstSlideNum eq 1>
                     1ST SLIDE: /photos/#MemberID##FirstSlideNum#.jpg
        <cfelse>
                     USE DEFAULT: /pics/placeholders/defaultimage.jpg
        </cfif>

Do you know if CFLOOP within CFOUTPUT of query

Possibly ... all depends on what the loop is doing. You should open a separate question for that one.
0
 

Author Comment

by:Ian White
ID: 38398048
Thanks my existing search already does a random sort - for fairness 50% of time and by join date the other half.

As there is only one unique member id in members do I need a group?  Does group conflict with order?

Also I have another table references similar to photos with member Id . Can I include that - that is another left join in same query.   The reason I just want to do one query is that when I output the report and do pagination I dont want to go off and do suplimentary reads for each record.

Thanks for your help- your blood is worth bottling. You should get tripple points for this
0
 

Author Comment

by:Ian White
ID: 38398146
Here is my dynamic query code just solving the two table join for now. Will create a new case for 3 table join.

If I omit the group I get an error

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Column 'Members.UserName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. <br>The error occurred on line 153.
.  

If I add the group I get error

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'GROUP'. <br>The error occurred on line 156.

 	<cfquery name="SearchResult" 
				datasource="Foo" 
				dbtype="ODBC"
                cachedWithin="#CreateTimeSpan(0,8,0,0)#">
								
     Select top 1000
			m.UserName,
			m.City, 
			m.State, 
			m.CountyDistrict, 
			m.CityTownArea, 
			m.Occupation, 
			m.AvailFromDate, 
			m.MemberType,
			m.CreationDate,
            m.Suburb,
			m.Headline,
			m.Age,
            m.Sex as Gender,
			m.Phone1AreaCode,
	        m.EmailAddress,
            m.Phone1Prefix,
            m.Phone1Extension,
            m.Phone2AreaCode,
            m.Phone2Prefix,
            m.Phone2Extension,
            m.FaxAreaCode,
            m.FaxPrefix,
            m.FaxExtension,
            m.MobilePhone,
            m.DisplayEmail,
            m.MembershipType,
            m.Notes,
            m.Country,
            m.keywords as Keywords,
	        m.MemberId as MemberId,
            MAX( CASE WHEN p.SlideNum = 1 THEN 1 ELSE 0 END ) AS FirstSlideNum,
            COUNT(p.SlideNum) NumOfSlides
            <!---
       	FROM Members  WITH (NOLOCK)
		--->
        
          FROM    Members m LEFT JOIN Photos2 p  WITH (NOLOCK)
    	             ON    p.memberID = m.memberID
        WHERE    m.MemberType   		= 'Sitter'
		AND      m.MembershipStatus  	= 'confirmed'
		AND      m.ActiveInactive   	= 'active'
		AND      m.ArchiveFlag       	= 'n'
		AND      m.DisplayAd         	= 'yes'
     	

            <cfif IsDefined("url.co")
			and len(trim(url.co))>
     		and (m.Country  = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="#url.co#">
                 OR m.AlternateCountries  LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' value="%#url.co#%">)
            </cfif>
            
            <cfif IsDefined("url.st")
            and len(trim(url.st))>
     		and m.state  like <cfqueryparam cfsqltype='CF_SQL_CHAR' value='%#url.st#%'>
            </cfif>
    <!---
            <cfif IsDefined("url.ci")
            and len(trim(url.ci))>
     		and url.ci LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='40' value='%#url.ci#%'>  
            </cfif>
     --->			
            
            <cfif IsDefined("url.cd")
            and len(trim(url.cd))>
                        
     		and m.countydistrict LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='40' value='%#url.cd#%'>  
            </cfif>
            
            <cfif IsDefined("url.cta")
			and len(trim(url.cta))>
     		and m.citytownarea LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='40' value='%#url.cta#%'>  
            </cfif>
            
            <cfif IsDefined("url.su")
			and len(trim(url.su))>
     		and m.suburb LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='40' value='%#url.su#%'>  
            </cfif>


            <!--- available date filter --->
            <cfif IsDefined("Form.AvailFromDate")
			and len(trim(Form.AvailFromDate))>
     		and m.AvailFromDate <= <cfqueryparam cfsqltype='CF_SQL_DATE'  value= #form.AvailFromDate#>  
            </cfif>
            
            <!--- flexiSearch --->
            <cfif IsDefined("url.Flexi")
			and len(trim(url.Flexi))>
     		and M.FlexiSearch = <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='1' value='y'>  
            </cfif>
                            
            <!--- Housesit Term required --->   
            <cfif IsDefined("url.HouseSitTerm")
			and len(trim(url.HouseSitTerm))>
            <cfset LoopCount = 0>
            <cfloop index="HouseSitTermFromList" list="#url.HouseSitTerm#">
            <cfset LoopCount = LoopCount + 1>
            <cfoutput>
     		and m.HouseSitTerm LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='20' value='%#HouseSitTermFromList#%'>
            </cfoutput>  
            </cfloop>
            </cfif>
            
            <!--- PetType loop through list to add criteria --->	
			<CFIF ISDEFINED ("url.PetType")
			and len(trim(url.PetType))>

			<cfloop index="PetTypeFromList" list="#url.PetType#">
            <cfoutput>
            and m.PetType LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR'  value='%#PetTypeFromList#%'>  
            </cfoutput>
            </cfloop>
            </CFIF>
            
            <!--- Key Words loop through list to add criteria --->	
			<CFIF ISDEFINED ("url.keywords")
			and len(trim(url.keywords))>

			<cfloop index="KeyWordsFromList" list="#url.keywords#">
            <cfoutput>
            and m.Keywords LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR'  value='%#KeyWordsFromList#%'>  
            </cfoutput>
            </cfloop>
            </CFIF>
       
        <cfif isDefined("sortByField")
		   and SortByField  is "AvailFromDate">
           ORDER BY m.AvailFromDate ASC, joinDate asc
        
         <cfelseif isDefined("sortByField")
		   and SortByField is  "random">
           ORDER BY NEWID()
         	
        <cfelse>
           ORDER BY m.MembershipType asc, joindate asc
        </cfif>    
        
        GROUP BY m.memberID, m.username  
        
        
  </cfquery>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 38398944

     As there is only one unique member id in members do I need a group?  

Yes. Generally when using aggregate functions, such as MAX/COUNT/etc... you must include a GROUP BY so the db knows how you want it to calculate the results. The rules can vary by db but usually all columns not involved in an aggregate function must be include in the group by.


     ... If I omit the group I get an error  ... If I add the group I get error

Sounds like a catch 22 doesn't it ;-) ?  You do need a group by, but that clause must be placed before the ORDER BY. ie

              SELECT  ColumnX, ColumnY              <=== SELECT always comes 1st
              FROM     TableName                         <=== FROM clause is 2nd
              WHERE   ColumnY = xxx                   <=== .. next is WHERE clause
              GROUP BY ColumnY                          <=== .. next is GROUP
              HAVING     COUNT(*) > 1                   <=== ... next is HAVING
              ORDER BY ColumnX                          <=== ORDER BY is last
     
So with your query, add all columns to the group by, except the MAX and COUNT part, and place it before the ORDER BY code.  

ie      SELECT TOP 1000
                m.UserName,
            m.City,
            ....etc
                m.Country,
                m.keywords as Keywords,
              m.MemberId as MemberId,
                MAX( CASE WHEN p.SlideNum = 1 THEN 1 ELSE 0 END ) AS FirstSlideNum,
                COUNT(p.SlideNum) NumOfSlides              
        FROM   .....
        WHERE ....
        GROUP BY
                m.UserName,
            m.City,
            ....etc
                m.Country,
                m.keywords,
              m.MemberId
         ORDER BY  ....                    <== must be last
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38398962
Also I have another table references similar to photos with member Id . Can I include that - that is another left join in same query.  

Most likely. Just remember when aggregate functions are involved, adding columns to the SELECT list -or- adding tables to the join can affect the totals **. But as long as the relationship between members and this 3rd table is 1-to-1 it should be fine.

** For example say you had 3 members:

      FirstName, LastName
      John, Smith
      John, Miller
      John, Adams

If you ran a count of members using only firstName

          SELECT FirstName, COUNT(*) AS NumOfRows
          FROM   Member
          GROUP BY FirstName

          results:    
         John,  3

those results would be very different than if you added the lastName column:

          SELECT FirstName, COUNT(*) AS NumOfRows
          FROM   Member
          GROUP BY FirstName

        results:
        John, Smith,  1
        John, Miller,  1
        John, Adams,  1
0
 

Author Comment

by:Ian White
ID: 38404236
Thanks I tried that - put the group before sort.

Then I get error message.  This was not throwing an error in my previous version without the orderby to get the count

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. <br>The error occurred on line 179.

New code

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. <br>The error occurred on line 179.

Open in new window

0
 

Author Comment

by:Ian White
ID: 38412167
Ok I am going to drop the count and group by

So all I want now is the join on 3 tables
members
photos
references

photos and references are identical structure keyed on memberId
0
 

Author Comment

by:Ian White
ID: 38412555
Thanks for all your help
the two left joins worked without the order and group, so I will live with that

 	<cfquery name="SearchResult" 
				datasource="foo" 
				dbtype="ODBC"
                cachedWithin="#CreateTimeSpan(0,8,0,0)#">
								
     Select top 1000
			m.UserName,
			m.City, 
			m.State, 
		    ....
            m.keywords as Keywords,
	        m.MemberId as MemberId,
            p.slidenum as FirstSlideNum,
            r.slidenum as FirstRefNum
          FROM    Members m LEFT JOIN Photos2 p  WITH (NOLOCK)
    	             ON    p.memberID = m.memberID
                     AND   p.SlideNum =  '1'	
                     
                     LEFT JOIN ReferenceDocs r  WITH (NOLOCK)
    	             ON    r.memberID = m.memberID
                     AND   r.SlideNum =  '1'	 

        WHERE    m.MemberType   		= 'Sitter'
		AND      m.MembershipStatus  	= 'confirmed'
		AND      m.ActiveInactive   	= 'active'
		AND      m.ArchiveFlag       	= 'n'
		AND      m.DisplayAd         	= 'yes'
        
         <cfif isDefined("sortByField")
		   and SortByField is  "random">
           ORDER BY NEWID()
         	
        <cfelse>
           ORDER BY m.MembershipType asc, joindate asc
        </cfif>    
        
  </cfquery>

Open in new window

0
 

Author Closing Comment

by:Ian White
ID: 38412559
Thanks for your help in understanding this difficult area of left join
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38437961
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

Sorry didn't see the last responses till now ...

Hm.. I didn't realize there were any text, ntext, or image columns involved. Those go by different rules than most other data types.  You'd either have to omit the offending field from the GROUP BY or possibly do a CAST.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Loops Section Overview
Screencast - Getting to Know the Pipeline

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question