John Rappold wrote:
> "Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
> news:1181830481.681955.25380@e9g2000prf.googlegrou ps.com...
>> On 14 Jun, 15:03, "John Rappold" <jrapp...@jscoca-k12.org> wrote:
>>> I have a table with the following fields:
>>> tableid (int)
>>> userid (varchar)
>>> lvalues (varchar)
>>>
>>> the lvalues column contains comma separted values. e.g. 12, 5, 32
>>> each individual values matches a field in another table.
>>>
>>> How do I write a SQL query that parses the comma separated data? For
>>> example:
>>> WHERE user id=30 so that the results look like:
>>>
>>> 30 | 12
>>> 30 | 5
>>> 30 |32
>>>
>>> TIA
>> You don't you build a table structure where each value has its own
>> record in another table.
>>
>> From:
>> http://dev.mysql.com/tech-resources/...alization.html
>> "The first normal form (or 1NF) requires that the values in each
>> column of a table are atomic. By atomic we mean that there are no sets
>> of values within a column."
>>
>
> This is a lookup table and the data is already there in the comma separated
> format. I can't change it.
>
> I'm thinking I need to do some sort of SELECT IN and then create a temporary
> table.
>
> Can anyone help me?
>
(Top posting fixed)
Paul is correct. While you might be able to get by with it this time,
you will be much better off in the long run to normalize your database.
Otherwise you will continue to run into similar problems.
In this case, since you have a many-to-many relationship, it's as simple
as building another table. This new table would have two columns - the
id of the original row in your first table, and one of the values from
the multiple-valued column, i.e. (Using names to be clearer - normally I
would be using numeric values for the id's):
Old table
Tom Venice, Rome, Paris, Berlin
Dick London, Copenhagen, Paris, Madrid
Harry London, Venice, Rome, Madrid
New table:
Tom Venice
Tom Rome
Tom Paris
Tom Berlin
Dick London
Dick Copenhagen
Dick Paris
Dick Madrid
Harry London
Harry Venice
Harry Rome
Harry Madrid
Now it is easy to select who's been to Rome (Tom and Harry), or where
Dick has been (London, Copenhagen, Paris and Madrid).
It may be difficult to change this now - but it's going to be even
harder later. And as long as you keep this design you will run into
more problems like this.
P.S. Please don't top post. Thanks.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================