Janine,
Errmmmm....
SELECT child.childid, child.childname, child.homeroomid, teacher.teachername
FROM teacher INNER JOIN (class INNER JOIN (child INNER JOIN class_child_link
ON child.childid = class_child_link.childid) ON class.classid =
class_child_link.classid) ON teacher.teacherid = class.classteacherid
ORDER BY teacher.teachername,child.homeroomid;
where tables are....
CHILD
childid
childname
homeroomid
TEACHER
teacherid
teachername
CLASS
ClassID
ClassTeacherID
CLASS_CHILD_LINK
ClassID
ChildID
Does this look anything like the tables you have??
I have to be honest and say for speed I cheated and set up the relationships
between the tables in access2000 and then generated a query using the
relationships to return all the classes sorted first by classteacherid and
then by homeroomid.
But the basic gist is that you actually have several nested queries there,
linking the teacher to a class and the class to the children using a link
table. The link table contains all the classes that a child attends.
The query above returns a query that looks like this :
childid childname homeroom teachername
4 Rob Reed 1 Mrs. Jones
5 Julie Jules 4 Mrs. Jones
2 Leigh Smith 1 Mrs. Smith
1 Tom Jones 1 Mrs. Smith
3 Kate Lee 2 Mrs. Smith
You would then do something like...
<cfoutput query="YourQuery" group="teachername">
#teachername#
<cfoutput>
#childname#
</cfoutput>
</cfoutput>
giving you....
Mrs Jones
Rob Reed
Julie Jules
Mrs Smith
Leigh Smith
Tom Jones
KateLee
Sorry that this is all a bit scary (particularly the INNER JOINS bit), but I
hope I've explained enough for you to get the general idea and be able to
look at what I've done and work it round to what you have.
Regards
Stephen
----- Original Message -----
From: "Janine Jakim" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, November 27, 2001 4:54 PM
Subject: RE: Contracting menus
> It does have a class code also. So to get a homeroom class you need
> teacherID and HomeCode=Home
> I'm just confused on how to group the students in their homeroom teacher
> while being accessed by other teacher....
>
> -----Original Message-----
> From: Christopher Olive [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, November 27, 2001 11:52 AM
> To: CF-Talk
> Subject: RE: Contracting menus
>
>
> add a class code for homeroom. treat it like a class.
>
> christopher olive, cto, vp of web development
> cresco technologies, inc
> 410.825.0383
> http://www.crescotech.com
>
>
> -----Original Message-----
> From: Janine Jakim [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, November 27, 2001 9:26 AM
> To: CF-Talk
> Subject: Contracting menus
>
>
> Ok maybe because it's early and the coffee hasn't kicked that I can't
> come
> up with a solution for this....
> I am making dynamic contracting menus for our school. This is for
> teachers
> such as math/science/music/band who have many students, but not all the
> students in the school. I want them to only see their students.
> They requested that the students be listed by Homeroom teacher- Like so.
> They click on the teacher and see the students.
> Mrs. Smith
> Tom Jones
> Leigh Smith
> Kate Lee
> Mrs. Jones
> Rob Reed
> Julie Jules
> etc....
> Mr. Andres
> Ms. Bell
>
> I'm having problems filtering my sql queries correctly to get the
> students....and either end up with all the students from all the
> homerooms
> or only the students listed under the teacher. (ie the math teacher will
> also have their own homeroom so s/he'll see all the math students listed
> under his/her name..)
> I have one query that gets all the homeroom teachers.
> What I need:
> All the students associated with the math/science/etc. teacher (hooks by
> a
> teacherID)
> and have them sorted by their homeroom teacher (teacherID)
> How the database is set up:
> Teachers are assigned according to subject and have a code. So there is
> a
> table that hooks teacherID, StudentID, ClassCode. So a student can have
> many different teachers....
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
