Rick Root wrote:
> On 2/20/08, erik tom <[EMAIL PROTECTED]> wrote:
>
>> The query is
>> select menuId,label,foundOn from dbo.menu where menuId IN (select foundOn
>> from dbo.menu where menuId=#form.selTopLevel2#) order by menuId
>>
> You're basically saying this:
>
> select menuId, label, foundOn from dbo.menu where menuId IN ('foo')
>
> What are the possible values of foundOn?
My earlier reply was truncated. foundOn is a poor implimentation of a
join table it contains a list of child menu ID of a top level menu ID.
I.E. select menuID, label, foundON from dbo.menu where muneID IN
(12,17,19,31).
This is the problem with storing a list of values in a single field
rather then properly normalizing your database and creating a join table.
'foundOn' is a list of values I.E. 1,6,13,21 this means it is not a
number or int field but rather some type of character or text field.
menuID is an INT field I presume.
INT(14) can not be matched to (1,6,13,21).
To do what you are trying to do here will need to be done in two steps.
First query the database for the list, then query the database for the
match of that list. This allows you to pass in the character list
through CF's loose typing and the database does not complain about you
trying to match an int field to a character field.
<cfquery name="theList"...>
select foundOn from dbo.menu where menuId=#form.selTopLevel2#
</cfquery>
<cfquery name="theResult"...>
select menuId,label,foundOn from dbo.menu where menuId IN
(#theList.foundOn#)
</cfquery>
Or better yet, normalize your database. Create a join table for foundOn
then you could do this much simplier join.
SELECT menuID,label,foundON
FROM dbo.menu JOIN dbo.joinTBL ON db.menu.menuID = dbo.joinTBL.childID
WHERE dbo.joinTBL.parentID = #form.selTopLevel#
This also has the advantage to not be limited to a single sub level, but
can have as many parent-child levels as you care to have.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299512
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
