Solved

Grouping problem: for every academic degree in one column, there are two certifications displayed in the next

Posted on 2011-09-25
19
235 Views
Last Modified: 2012-05-12
I've been staring at this grouping problem for some time now. I have a directory of Careplanners:

http://careplanners.net/directory.cfm

As you can see, there are repetitions in the Certifications column. The pattern is, for every single academic degree listed, there is an extra certification listed in the next column. Thus, if Susan has earned two degrees -- BA and Diploma Nursing -- then each of her certifications appears twice:

CLCP CCM CLCP CCM

I've tried different ideas in the QUERY and the OUTPUT for a few hours. What am I missing?

Maybe this will make sense to me in the morning. Thanks as always for your help. Eric

QUERY (sorry, this is a huge query, joining five related tables):

<!--- query getCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="getCareplanners">
SELECT
<!--- select from parent table, CareplannersMembers cp --->
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info

<!---===== select from child tables and relational tables =====--->
<!--- cdegree = careplanners_degrees --->
<!--- chdegree = careplanners_has_degrees --->
,cdegree.degreeID
,cdegree.DegreeTitle
,chdegree.CareplannersID
,chdegree.degreeID

<!--- ccert = careplanners_certifications --->
<!--- chcert = careplanners_has_certifications --->
,ccert.certificationID
,ccert.certificationTitle
,chcert.CareplannersID
,chcert.certificationID

<!--- cped = careplanners_pediatric --->
<!--- chped = careplanners_has_pediatric --->
,cped.pediatricID
,cped.pediatricTitle
,chped.CareplannersID
,chped.pediatricID

<!--- cadu = careplanners_adult --->
<!--- chadu = careplanners_has_adult --->
,cadu.adultID
,cadu.adultTitle
,chadu.CareplannersID
,chadu.adultID

<!--- creg = careplanners_regions --->
<!--- chreg = careplanners_has_regions --->
,creg.regionID
,creg.region
,chreg.CareplannersID
,chreg.regionID

<!--- select from parent table --->
FROM CareplannersMembers cp

<!--- join the related tables and child tables --->
LEFT OUTER JOIN careplanners_has_degrees chdegree
ON cp.CareplannersID = chdegree.CareplannersID
LEFT OUTER JOIN careplanners_degrees cdegree
ON cdegree.degreeID = chdegree.degreeID

LEFT OUTER JOIN careplanners_has_certifications chcert
ON cp.CareplannersID = chcert.CareplannersID
LEFT OUTER JOIN careplanners_certifications ccert
ON ccert.certificationID = chcert.certificationID

LEFT OUTER JOIN careplanners_has_pediatric chped
ON cp.CareplannersID = chped.CareplannersID
LEFT OUTER JOIN careplanners_pediatric cped
ON cped.pediatricID = chped.pediatricID

LEFT OUTER JOIN careplanners_has_adult chadu
ON cp.CareplannersID = chadu.CareplannersID
LEFT OUTER JOIN careplanners_adult cadu
ON cadu.adultID = chadu.adultID

LEFT OUTER JOIN careplanners_has_regions chreg
ON cp.CareplannersID = chreg.CareplannersID
LEFT OUTER JOIN careplanners_regions creg
ON creg.regionID = chreg.regionID

  ORDER BY
       <cfswitch expression="#sortBy#">  
                <cfcase value="FirstName">FirstName</cfcase>  
                <cfcase value="LastName">LastName</cfcase>
                <cfcase value="city1">city1</cfcase>  
                <cfcase value="practice">practice</cfcase>
                <cfcase value="state1">state1</cfcase>  
                <cfcase value="DegreeTitle">DegreeTitle</cfcase>
                <cfcase value="certificationTitle">certificationTitle</cfcase>
                <cfdefaultcase>cp.CareplannersID</cfdefaultcase>  
           </cfswitch>  
         <cfif url.sortDir eq "DESC">
             DESC
       <cfelse>
             ASC
       </cfif>
  
  </cfquery>




OUTPUT: (I'm leaving in some of the surrounding code so you can see better what is going on)
 <!--- list Careplanners --->
 <cfoutput query="getCareplanners" group="CareplannersID" startRow="#URL.StartRow#" maxRows="#RowsPerPage#">
 
 
 <cfif bgColor neq "##ffffff">
    <cfset bgcolor="##ffffff">
  <cfelse>
    <cfset bgcolor="##f7f5f5">
  </cfif>

  <tr style="background-color:#bgcolor#;">


<td><a style="text-decoration:none !important;" href="/displayCareplanner.cfm?CareplannersID=#getCareplanners.CareplannersID#"><img src="img/smallsearch.gif" width="13" height="13" alt="View" class="border0" /></a></td>

   <td>
   
#getCareplanners.CareplannersID#
   </td>



   
  <!--- FirstName --->
   <td class="left">#getCareplanners.FirstName#</td>
  <!--- LastName --->
   <td class="left">#getCareplanners.LastName#</td>
  <!--- City --->
   <td class="left">#getCareplanners.city1#</td>
  <!--- State --->
   <td class="left">#getCareplanners.state1#</td>
  <!--- Practice --->
   <td class="left">#getCareplanners.practice#</td>
  <!--- Degrees --->
   <td class="left">
<cfoutput group="DegreeTitle" groupcasesensitive="no">
#getCareplanners.DegreeTitle#
</cfoutput> 
   
   </td>
  <!--- Certifications --->
   <td class="left">

<cfoutput group="certificationTitle" groupcasesensitive="no">
#getCareplanners.certificationTitle#
</cfoutput> 

   
   </td>
 


  </tr>
 </cfoutput>

Open in new window

0
Comment
Question by:Eric Bourland
  • 8
  • 7
  • 4
19 Comments
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
Hey Eric,
Try like this...  you place the "Group=" attribute on the outer cfoutput, not the inner ones..


   <td class="left">
<cfoutput>
#getCareplanners.DegreeTitle#
</cfoutput>
   </td>
  <!--- Certifications --->
   <td class="left">
<cfoutput>
#getCareplanners.certificationTitle#
</cfoutput>
   </td>
 
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria,

Godo to hear from you.

When I take away the group from the inner outputs, I get multiple results in degrees and certifications columns:

http://careplanners.net/directory.cfm

So, I think I need to edit my query. I was trying SELECT DISTINCT but that did not help either.

I know there is a way to do this. I'm looking again at the query.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 167 total points
Comment Utility
My caffeine starved brain isn't up for a round "how to make cfoutput group smarter" ;-)  But I seem to remember we've had this problem before.  Maybe pointing out the 2 problems I see might help?

for every single academic degree listed, there is an extra certification listed in the next column.

1) That's exactly what I'd expect.  remember cfoutput group is a nice feature, but it's very dumb. For it to do what you want, the results would have to be ordered by both "degree" and "certification" simultaneously - which isn't possible in sql.  You could generate a distinct list of both manually. But then there's still issue #2 ...

ORDER BY
       <cfswitch expression="#sortBy#">  
            <cfcase value="FirstName">FirstName</cfcase>  
          <cfcase value="LastName">LastName</cfcase>
           <cfcase value="city1">city1</cfcase>  
           <cfcase value="practice">practice</cfcase>
     ....
     <cfoutput query="getCareplanners" group="CareplannersID" ...>

2) remember your sql ORDER BY must match the way you're grouping the output.  If you're dynamically changing the order, so they're no longer sorted by "CareplannersID" the grouping won't work properly.


0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>>remember your sql ORDER BY must match the way you're grouping the output.

I remember this, and I noticed the ORDER BY does not match the grouping.

I think I need to give up the dynamic sorting at the top of the page. I'm going to add a search tool anyway.

gd and _agx_, these are very helpful comments as always, and I am thinking about them.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
ya, I'm with agx.. the problem is that you're joining multiple tables with multiple children, that means every child record will be duplicated for every other child record... giving you lots of children.. (octomom)

I would take a different approach, try this dummy-down version that pulls a list of degrees into a single column


SELECT
   cp.CareplannersUUID
  ,cp.CareplannersID
  ,cp.prefix
  ,cp.firstname
  ,cp.middleinit
  ,cp.lastname
  ,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 )
FROM CareplannersMembers cp

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 167 total points
Comment Utility
Yeah, cfoutput grouping doesn't do well with pagination and sorting IMO

If you dropped the dynamic sorting, you could generate the lists of distinct values manually like I mentioned.  Something along these lines ...

<cfoutput query="getCareplanners" group="CareplannersID" ...>
   <td>#getCareplanners.CareplannersID#</td>
   ...

  <!--- Initialize 2 arrays before the inner loop. --->
  <cfset uniqueDegrees = []>
  <cfset uniqueCertifications = []>

  <cfoutput>
        <!--- Inside the loop, track the distinct values --->
        <cfif not ArrayFindNoCase(uniqueDegrees, DegreeTitle)>
                <cfset arrayAppend(uniqueDegrees, DegreeTitle)>
        </cfif>
        <cfif not ArrayFindNoCase(uniqueCertifications, certificationTitle)>
                <cfset arrayAppend(uniqueCertifications, certificationTitle)>
        </cfif>
  </cfoutput>

  <!--- finally, output the distinct values ---->

   <td class="left">#ArrayToList(uniqueDegrees)#</td>
   <td class="left">#ArrayToList(uniqueCertifications)#</td>
   ...
</cfoutput>
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
> I would take a different approach

It's role reversal.  lol.  I was going to suggest XML PATH, but wasn't sure if Eric wanted to keep it at the db or CF level.  But FWIW that's what I'd use :)

(Just don't over do it. Having too many XML PATH's in one query can tank performance).
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I'm going to try both methods -- mainly, I want to see how each might work.

I gotta switch to another task for a little while then will get back to this Careplanners task. Thank you both. Hope your Monday is going well.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Yep try them out. Either would work, but XML PATH is slicker for smaller queries.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Sorry, I had to take care of a couple of other problems until late last night, and most of today.

This is my simplified query

<!--- query getCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="getCareplanners">
SELECT
<!--- select from parent table, CareplannersMembers cp --->
cp.CareplannersUUID
,cp.CareplannersID
,firstname
,lastname
,practice
,city1
,state1
,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 
,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
                           FROM careplanners_has_certifications hc
                            inner join careplanners_certifications c on hc.certificationID = c.certificationID
                           WHERE hc.CareplannersID = cp.CareplannersID
                           ORDER BY c.certificationTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 

FROM CareplannersMembers cp
ORDER BY lastname, DegreeTitle, certificationTitle
  </cfquery>

Open in new window


But I get this error:
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'DegreeTitle'.  
The error occurred in C:/websites/www.careplanners.net/directoryTest.cfm: line 24
23 : <!--- query getCareplanners, select columns from table CareplannersMembers --->
24 : <cfquery datasource="#application.datasource#" name="getCareplanners">
25 : SELECT
26 : <!--- select from parent table, CareplannersMembers cp --->

Column DegreeTitle does exist in table careplanners_degrees. I think ColdFusion thinks I am requesting column 'DegreeTitle' from parent table, CareplannersMembers? Hmm. I think I can just add careplanners_degrees in the FROM clause. I will try that. But I wanted to check in here and let you know I am working on this task. =)

Hope your respective days are going well.

Eric
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
The problem is with the degreeTitle in your order by clause...

Since you are no longer joining tables, you need to remove it from the order by.  You can't order by a column in a sub query...

ORDER BY lastname, DegreeTitle, certificationTitle
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Ah! I see. I get it.

OK, that worked.

Now, ColdFusion says variable DegreeTitle is not defined in the scope of my query, getCareplanners:

Element DEGREETITLE is undefined in GETCAREPLANNERS.
The error occurred in C:/websites/www.careplanners.net/directoryTest.cfm: line 165
163 :    <td class="left">
164 : <cfoutput>
165 : #getCareplanners.DegreeTitle#
166 : </cfoutput>

But when I remove the getCareplanners scope, then the variable DegreeTitle is undefined. How should I define the scope of variable DegreeTitle? (I feel like this should be obvious to me, given all that I have learned here.)

   <td class="left">
<cfoutput>
#getCareplanners.DegreeTitle#
</cfoutput>
   
   </td>
  <!--- Certifications --->
   <td class="left">

<cfoutput>
#getCareplanners.certificationTitle#
</cfoutput>
   </td>
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility

The value is assigned to a column alias called degreeTitleList

,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )

That is the name to use in the cfquery scope, it will be a comma delimited list of degree titles.   You can remove the commas and just display with spaces doing this..

#listChangeDelims(myQuery.degreeTitleList," ")#


0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Holy bananas. That works amazingly well: http://careplanners.net/directory.cfm

And I did not know about the ListChangeDelims function. I just read up on that function at help.adobe.com. That is very useful.

I'm going to come back later and close this question. Great success, and as usual I learned a ton.

Thank you gdemaria and _agx_.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
     >  You can remove the commas and just display with spaces doing this..

Or just use make the delimiter a space to begin with.  Though if you think you'll be using list functions (and the degree title might contain commas or spaces) I'd use a less common delimiter.
     
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
> That works amazingly well: http://careplanners.net/directory.cfm

Looks good. Though maybe use a line break in between? So the titles don't all run together.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
_agx_, good idea. I added a break:

<cfoutput>
#listChangeDelims(getCareplanners.degreeTitleList,"<br />")#
</cfoutput>

And it looks better. I need to format that page to make it more readable.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Yep, much better.
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
This worked really well. ColdFusion, and gdemaria and _agx_, save the day yet again. I learned some very useful things about ordering data from multiple columns. Thank you as always.

Eric B
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now