2 Replies - 17310 Views - Last Post: 18 March 2005 - 09:54 AM Rate Topic: -----

#1 philiprakusen   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 09-December 04

Sql - Find A Value From A List

Posted 18 March 2005 - 09:08 AM

I have a column called area_id_list and it contains a list of area_ids, e.g. '124,412,12,415,120'.

Is there a SQL Statement that can return the rows that contain a specific area_id, e.g. 12?

All I can think of is: WHERE area_id_list LIKE '%12%', but that would also return the rows that contain 124 and 120.

Is there a better way than creating a new row in the table for each area_id?
Is This A Good Question/Topic? 0
  • +

Replies To: Sql - Find A Value From A List

#2 malkiri   User is offline

  • D.I.C Regular
  • member icon

Reputation: 3
  • View blog
  • Posts: 364
  • Joined: 29-March 01

Re: Sql - Find A Value From A List

Posted 18 March 2005 - 09:52 AM

The proper way to do this would be to break the area id data into a second table. Let's say your current table is table1 with primary key key1. Make a second table areaTable with foreign key areaKey and an area_id column. Now when you want to know what area_ids a row in table1 has, do a join on the two tables:

SELECT key1, area_id
FROM table1 INNER JOIN areaTable on table1.key1 = areaTable.areaKey[code]

This would return the data like the following:

key1    area_id
1       124
1       412
1       12
1       415
1       120
2       598
2       595
3       91

So if you wanted to select only rows in table1 that have an area_id of 12, you can do the following:

SELECT key1, aColumn, anotherColumn
FROM table1 INNER JOIN areaTable on table1.key1 = areaTable.areaKey
WHERE area_id = 12

If you're not familiar with join types, here's a good explanation:

Incidentally, the above process is called normalization. There are several different levels of normalization increasing in restrictions, from first normal form to fifth normal form. What I described above fulfills first normal form. You can find more information on normalization here: http://dev.mysql.com...malization.html
Was This Post Helpful? 0
  • +
  • -

#3 Amadeus   User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 250
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Sql - Find A Value From A List

Posted 18 March 2005 - 09:54 AM

Hmm...with multiple entries in each field, you may be stuck with the LIKE as a simple statement...the IN statement does what you'd like, but is meant for the other situation (multiple possibilities, one field value)...if this is part of a program/scritp, you can use that languages string manipulation functions to conver the field value to a string, specify what you want, then reconvert to a number, although that is probably not required as I expect the multiple value field is a text string anyway.

[edit]malkiri beat me to it, and his normalization suggestion is the proper way to go...my suggestion is meant for those who do not want to change the db table structure...[/edit]

This post has been edited by Amadeus: 18 March 2005 - 09:56 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1