Thanks Adam (and Steve), Luckily elegance is not a requirement, so your solution works well.
Unfortunately it is part of a legacy db so I have no control over how the data is stored... I'm just the monkey who has to make it work at the front-end! Steve C -----Original Message----- From: Adam Chapman [mailto:[EMAIL PROTECTED] Sent: Tuesday, 3 June 2003 4:49 PM To: CFAussie Mailing List Subject: [cfaussie] RE: SQL help required Hey Steve, Not all that elegant (or performance friendly) but.. SELECT distinct(acct_code) FROM coarext WHERE cmpy_code = '01' AND ( -- only element in the field resp_e_mail = '#request.session.user.username#' OR -- first element in the 'list' resp_e_mail like '#request.session.user.username#,%' OR -- somewhere in the middle resp_e_mail like '%,#request.session.user.username#,%' OR -- last element in the 'list' resp_e_mail like '%,#request.session.user.username#' ) Seeya, Adam -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 4:26 PM To: CFAussie Mailing List Subject: [cfaussie] SQL help required Hi, I have a query like so: SELECT distinct(acct_code) FROM coarext WHERE cmpy_code = '01' AND resp_e_mail like '%#request.session.user.username#%' values in the resp_e_mail field might include something like this: cliftosg or this: cliftosg,brownej,smithjh the value of #request.session.user.username# is always a single username like "cliftosg". the problem with my query above is that the username "browne" and the username "brownej" will be selected in the statement if the value of resp_e_mail is "cliftosg,brownej,smithjh". how can i amend the SQL statement to take this into account? i need to use listfind() or something but am unsure in SQL. thanks.... steve --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
