Thanks Janine. I think my problem stems from my use of a tree structure for my table - I should have done it in three separate tables and not tried to be clever!
K. "Janine Jakim" <[EMAIL PROTECTED]> wrote in message news:<1BEB99C913B7D411855F00B0D020F05B2B05C6@STMS>... > Kay, > When I made my 3 select I resorted to doing it in access and then > modifying the sql that i needed to make the 3 select work. It's a > huge burdensome query and I don't know if it's overkill or not. I'm > showing you my query so you'll have an idea about what i did. Mine > was for a school the user picks a grade then picks a teacher then can > pick student(s). I've taken three tables for this: Teacher, Student > and Class tables. (my client.schoolnum maybe confusing- to make a > unique teacherid we need the school number and the teacher number). I > added my formatting inside my query because I couldn't find a way to > do it otherwise (LASTNAME + ', '+FIRSTNAME AS NAME) I hope this is > helpful... j > > <CFQUERY NAME="GradeTeachStudent" DATASOURCE="#Request.dsn#"> > > SELECT Teachers.GRADE, Students.GRADE, > Students.LASTNAME, Students.FIRSTNAME, > CLASS.CLASSID, LASTNAME +', '+ FIRSTNAME AS NAME, > CLASS.SCHOOLNUM, CLASS.TeachType, > Students.ID, Teachers.TCHNAME > FROM Teachers INNER JOIN (CLASS INNER JOIN Students ON CLASS.ID = > Students.ID) ON (Teachers.CLASSLINK = CLASS.CLASSID) AND (Teachers.SCHOOLNUM > = CLASS.SCHOOLNUM) > WHERE Teachers.SCHOOLNUM=#CLIENT.SCHOOLNUM# > > AND (CLASS.TeachType='Homeroom') > AND CLASS.CLASSID=Teachers.CLASSID > AND CLASS.ID =Students.ID > ORDER BY Students.GRADE, Teachers.TCHNAME, > Students.LASTNAME > </CFQUERY> > > > > -----Original Message----- > From: Kay Smoljak [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, December 11, 2001 8:43 AM > To: CF-Talk > Subject: 3 selects related and nightmare trees > > > Hi all, > > I posted this on the HouseOfFusion SQL list but haven't had any > bites... Can anyone help? > > I have a "limited tree" kind of deal going with a table of categories > - the structure is like so: > > pkCategories category_name fkParentCategory > > To save it from turning into absolute hell, there can only be three > categories deep - so a category can have a subcategory which can have > subcategories, but that's as far as it goes. > > I want to use Nate Weiss's custom tag "three selects related". To use > it I need to create a query object with the following structure: > > grandparentID grandparentname parentID parentname childID > childname > > I have no idea how to write such a beast. I started out with: > > SELECT A.pkCategories, A.category_name, A.fkParentCategory, > ( SELECT B.pkCategories, B.category_name, > B.fkParentCategory > FROM Categories B > WHERE B.pkCategories = A.fkParentCategory > ) > FROM Categories A > > But I'm not really sure what I'm doing or where I'm going :) > > If it's not possible with SQL, I could use CF's query functions to > create what I need. That requires a whole rethink though. > > Any help greatly appreciated! > > Kay. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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
