SQL / Coldfusion display question

[Newsgroup_User]

I am not sure if this can be done in one SQL pull / Coldfusion display or
not. If a table has an employee name and department, can you list the people
using the department in the title and listing the people below it.

i.e.)

Table: Employees

employeeName department
---------------- -------------
Steve Smith Admissions
Susan Day Admissions
Bob Turner HR
Billy West HR
Tom Jones Accounting


And output this to a page:

<b>Admissions</b>
Steve Smith
Susan Day

<b>HR</b>
Bob Turner
Billy West

<b>Accounting</b>
Tom Jones

Thanks.
--
Wally Kolcz
MyNextPet.org
Founder / Developer
586.871.4126

[Newsgroup_User]

Very easily, check out the <cfoutput ... group=""> parameter.

You will want to order the query by the department then employee. The
slightly un-intuitive aspect of the group parameter of the <cfquery...>
tag is that it is based on the 'ORDER BY' SQL clause NOT the 'GROUP BY'
clause.

[Dan_Bracuk]

Yes. Use the group attribute of the cfoutput tag. Details are in the cfml reference manual. If you don't have one, the internet does.

Make sure you sort your query by the field you are going to group on.

[ThreeRavens]

That would work and is probably more efficient...you can also order by department, name then check what department you are on and if it is deiffernt...display the new department and list the name under it. Dan's way is the proper way though 😉

[cf_dev2]

Yes, both suggestions will work. As it was already typed, here's an untested cfoutput example.

[Newsgroup_User]

Wow, that didn't work at all :-)

I got all the employees listed under all the departments.

<cfquery name="deptDirectory" datasource="sql_abt">
Select s.firstname, s.lastname, s.ext, s.title, s.email, d.dept
FROM lsstaff S, lsdepts D
Where s.deptid = d.deptid
AND s.rank <> 2 OR s.rank <> 5
ORDER BY d.dept
</cfquery>

And then used the suggested CFML to output it....

<cfset deptDirectory = viewState.getValue("deptDirectory")>
<cfoutput query="deptDirectory" group="dept">
<b>#dept#</b><br>
<cfoutput>
<table width="98%" border="0" align="center">
<tr>
<td>#firstname# #lastname#</td>
<td>#title#</td>
<td>
<cfif LEN(#ext#) GT 4>
#ext#
<cfelse>
734.827.#ext#
</cfif></td>
</tr>
</table>
<br></cfoutput>
<br>
</cfoutput>

What did I miss?

[Dan_Bracuk]

quote:

---

Originally posted by: Newsgroup User
Wow, that didn't work at all :-)

I got all the employees listed under all the departments.

<cfquery name="deptDirectory" datasource="sql_abt">
Select s.firstname, s.lastname, s.ext, s.title, s.email, d.dept
FROM lsstaff S, lsdepts D
Where s.deptid = d.deptid
AND s.rank <> 2 OR s.rank <> 5
ORDER BY d.dept
</cfquery>

And then used the suggested CFML to output it....

<cfset deptDirectory = viewState.getValue("deptDirectory")>
<cfoutput query="deptDirectory" group="dept">
<b>#dept#</b><br>
<cfoutput>
<table width="98%" border="0" align="center">
<tr>
<td>#firstname# #lastname#</td>
<td>#title#</td>
<td>
<cfif LEN(#ext#) GT 4>
#ext#
<cfelse>
734.827.#ext#
</cfif></td>
</tr>
</table>
<br></cfoutput>
<br>
</cfoutput>

What did I miss?

---

My guess is that you missed column aliases in the select clause.

[Newsgroup_User]

Actually, for anyone else out there, I messed the WHERE clause of rank. I
changed it and it worked like a charm.

Thanks everyone!

[Newsgroup_User]

No obvious syntax errors that I can see. So HOW did it not work at all?
Error messages? Improper data? Improper display? What went awry?

P.S.
WHERE s.deptid = d.deptid
AND s.rank <> 2 OR s.rank <> 5

This looks a bit odd to me. Would this not return all rank values? I
presume every rank value is going to either not be two OR not be five?

[Newsgroup_User]

It looped everyone in each department header.

Admissions
Everyone in the database

Academic Affairs
Everyone in the database

Career Services
Everyone in the database

[Newsgroup_User]

Nevermind, got it to work.