How about:
<CFQUERY BLOCKFACTOR="100" NAME="GetChildren" DataSource="WhateverIsYourDSN"
DBType="ODBC">
SELECT DISTINCT
A.pkCategories AS grandparentID,
A.category_name AS grandparentname,
B.pkCategories AS parentID,
B.category_name AS parentname,
C.pkCategories AS childID,
C.category_name AS childname
FROM
Categories C INNER JOIN
Categories B ON
C.fkParentCategory = B.pkCategories INNER JOIN
Categories A ON
B.fkParentCategory = A.pkCategories
</CFQUERY>
Then:
<BR><BR><FONT SIZE="2"><B>Categories A:</B></FONT><BR>
<CF_ThreeSelectsRelated
QUERY="GetChildren"
HTMLAfter1="<BR><BR><FONT SIZE=""2""><B>Categories B:</B></FONT><BR>"
HTMLAFTER2="<BR><BR><FONT SIZE=""2""><B>Categories C:</B></FONT><BR>"
NAME1="grandparentID"
NAME2="parentID"
NAME3="childID"
VALUE1="grandparentID"
VALUE2="parentID"
VALUE3="childID"
DISPLAY1="grandparentname"
DISPLAY2="parentname"
DISPLAY3="childname"
DEFAULT1="1"
SIZE1="4"
SIZE2="4"
SIZE3="4"
WIDTH1="350"
WIDTH2="350"
WIDTH3="350"
FORCEWIDTH1="40"
FORCEWIDTH2="40"
FORCEWIDTH3="90"
AUTOSELECTFIRST="No"
MULTIPLE3="No"
>
-----Original Message-----
From: Kay Smoljak [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 11, 2001 1:37 AM
To: SQL
Subject: 3 selects related and nightmare trees
Hi all,
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
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
