Hi Everyone.
I require some help with an IN statement I'm trying to get working. I have
inherited a database from someone else in which there is a table with
project information and a field containing which geographic regions the
projects relate. As a single project can relate to multiple geographic
regions the person who created the database create this field as a varchar
field and populated it with a pipe (|) delimited list of ID's of the
geographic regions (ID from another table). An example of the data in this
field is:
1|5|9|10|12
Or
1
Or
9|5|7
I have been asked to write a query to return all the projects for a single
geographic region. This is what I have come up with:
SELECT project_title
FROM projects
WHERE 9 IN (REPLACE(geo_region,'|',','))
ORDER BY project
Where 9 is the geographic region ID I'm looking for and geo_region is the
field containing the pipe delimited list. Now with the above three lines of
data I would have expected it to return 2 rows, however it only returns one
(the last one). If I change the number to 5, I would expect 2 rows as well,
however it doesn't return any. It only appears to return a row if the number
appears at the beginning of the list!!! I'm not sure where I'm going wrong,
or even if what I'm trying to do is possible (please say it is!!!). Any
advise would be very gratefully received.
Best Regards,
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
