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
