The code will only match on the first user_del row because you're looping
thru the del_locn table.
I solve this using an outer join on a nested query (in SQL Server):
SELECT dlo_id,
dlo_locn,
dlo_selected = CASE WHEN id_list.dlo_id = del_locn.dlo_id IS
NULL THEN 'N' ELSE 'Y' END
FROM dlo_locn LEFT OUTER JOIN (SELECT dlo_id
FROM user_del
WHERE usr_id = #user_id#) AS
id_list
ON dlo_locn.dlo_id = id_list.dlo_id
#dlo_selected# will be 'Y' for user selected locations, 'N' for the others.
This only works if the DB supports features like sub-queries and outer
joins, but it should be a bit quicker than writing procedural CF to achieve
the same result.
--
Paul Wakefield
All opinions expressed herein are those of the author and not of The Board
of Executors
> -----Original Message-----
> From: Ricq Pattay [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, April 11, 2000 3:33 PM
> To: [EMAIL PROTECTED]
> Subject: pre-selecting items in a multiple list box
>
>
> I'm using a CF template that attempts to pre-select several
> items in a list
> box based on values returned from 2 queries. If a match
> occurs, that entry
> in the list box should be selected. There will almost always
> be more than
> one match. Specifically, the list box should list all
> possible delivery
> locations (from the get_del_locn query) and highlight/pre-select those
> locations that a user has a record returned for in
> get_usr_del_locn. What
> the code below produces *almost* works - all possible
> delivery locations are
> in the list box, but only *one* of the user's specific
> locations is selected
> even though they might have 4 or 5 locations tied to their usr_id.
>
> So... Why doesn't this code work?
>
> <!--- get a user's specific delivery locations --->
> <cfquery datasource="centsup_dev" name="get_user_del_locn"
> debug="yes">
> select user_del.dlo_id
> from del_locn, user_del
> where del_locn.dlo_id = user_del.dlo_id
> and user_del.usr_id = #usr_id#
> </cfquery>
>
> <!--- get all possible delivery locations --->
> <cfquery datasource="centsup_dev" name="get_del_locn" debug="yes">
> select dlo_id, dlo_locn
> from del_locn
> order by dlo_locn
> </cfquery>
>
> <!--- create the list box; attempt to pre-select user's
> location(s) --->
> <select name="dlo_id" size =5 width=150 multiple>
> <cfoutput query="get_del_locn">
> <cfif #get_user_del_locn.dlo_id# is #get_del_locn.dlo_id#>
> <option value=#get_del_locn.dlo_id#
> selected>#get_del_locn.dlo_locn#
> <cfelse>
> <option value=#get_del_locn.dlo_id#>#get_del_locn.dlo_locn#
> </cfif>
> </cfoutput>
> </select>
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~
> Ricq Pattay <[EMAIL PROTECTED]>
> Univ of MN College of Veterinary Medicine
>
>
>
>
> --------------------------------------------------------------
> ----------------
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
