----- Original Message ----- From: "Andrew Dixon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 02, 2004 9:44 AM Subject: In Statement Help
> 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. > Do you have any option of redesigning the database? Putting multiple values in a single "cell" (intersection of a row and column) is very bad form indeed for a database. It would be far better design to store each of the regions in their own separate table connected to the original table via a foreign key. Something like this: PROJECT +----------------+--------------+ | project_number | project_name | +----------------+--------------+ | 1 | Alpha | | 2 | Beta | | 3 | Gamma | +----------------+--------------+ Primary key: project_number REGION +---------------+---------------+ | region_number | region_name | +---------------+---------------+ | 1 | North America | | 2 | Europe | | 3 | Africa | | 4 | Australia | | 5 | South America | | 6 | Asia | +---------------+---------------+ Primary key: region_number PROJECT_REGION +----------------+---------------+ | project_number | region_number | +----------------+---------------+ | 1 | 1 | | 1 | 2 | | 1 | 4 | | 2 | 1 | | 2 | 3 | | 3 | 6 | +----------------+---------------+ Primary key: project_number, region_number Foreign key: project_number references PROJECT table Foreign key: region_number references REGION table Then the query to get the projects that are running in Region 1 is: select p.project_number, project_name from project p inner join project_region pr on p.project_number = pr.project_number where pr.region_number = '1'; If you know the name of the Region that you want but don't know it's number, this query will do the job: select p.project_number, project_name from project p inner join project_region pr on p.project_number = pr.project_number inner join region r on r.region_number = pr.region_number where r.region_name = 'North America'; This is *far* nicer and easier than to have to mess about with parsing individual elements of a single field. By the way, in devising this modified data design, I've made the assumption that the relationship between projects and regions is *not* one-to-many but is actually many-to-many. In other words, I think it is true that a given region can have several projects *and* that a given project can be operating in several regions. That's why I created the PROJECT_REGION table: it shows the *combinations* of projects and regions. PROJECT_REGION is called an 'intersection' table (or sometimes an 'association' table). The intersection table ensures that you can determine all the projects that run in a given region or that you can show all the regions in which a given project is running. This design is very robust and will support an infinite number of projects and regions. Any project can be running in as many regions as you like. I realize that you might not be at liberty to modify the table design to match what I've suggested but I would highly recommend that you consider it. It will make your job a *lot* easier ;-) Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
