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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

>>>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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

> 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).

ASKER

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.

Yep try them out. Either would work, but XML PATH is slicker for smaller queries.

ASKER

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 : 
24 : <cfquery datasource="#application.datasource#" name="getCareplanners">
25 : SELECT
26 : 

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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>

<td class="left">

<cfoutput>
#getCareplanners.certificationTitle#
</cfoutput>
</td>

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

> 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.

> 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.

ASKER

_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.

Yep, much better.