RegEx Comparison of row elements?

rulian

Okay, I'm starting to go MySQL happy now... here is my current situation.

I have to exclude rows on a JOIN where elements of the row are reoccuring in another tables rows, meaning I have a table where I have a list of ID's

| Table ID | Name | List of IDs |
| 1 | A | 2, 3, 5 |
| 2 | B | 1, 4, 5 |
| 3 | C | 1, 4 |
| 4 | D | 2, 3, 5 |

So what I need is to search the LIST fieds for A and B
and exclude numbers that match each other, so FOR A I would pull 2, 3, 5, but for B I would only pull 1, and 4, or even if I must combine the list first...

I dont believe this is even possible without setting variables in mysql which I've seen done, I know I can do this with reg ex in php, but I rather do it in mysql to save myself from pulling alot of unneccesary information.

any ideas where to start?

sid

this is not the most efficient way, but did you try

WHERE List_of_IDs NOT LIKE '%1%'
OR List_of_IDs NOT LIKE '%5%'[code]
to exclude id 5 and 1? of course this only works if there aren't id's with more than one digit.

not knowing your database layout, it seems to be a lot more efficient to have a second table, that matches Table ID to the IDs from the 3rd field.
like
[code]
| Table ID | Name |
+----------+------+
|        1 |    A |

| Table ID | IDs |
+----------+-----+
|        1 |   2 |
|        1 |   3 |
|        1 |   5 |

instead of

| Table ID | Name | List of IDs |
+----------+------+-------------+
|        1 |    A | 2, 3, 5     |

rulian

hello Sid

thank you for your help.
Originally I did have 2 seperate tables, however, the number of entries would get to be to large after a short while because this list is hierarchial, so each member could eventually have hundreds of numbers, each having hundreds subcats and so fourth.

So I'm keeing my lists more flat to avoid having a million rows.

sid

i still don't see the use of why you are doing it like this. i doubt that mysql is going to buckle because of the data amount of a table that has 2 integer fields, even if that's a few million rows big (not to mention, that the data really doesn't get less, just because it's contained in a text field). datamodel-wise i think it would definitely the cleaner way to go with 2 tables. plus your initial problem would be solved in an instant this way.

nemonoman

Sid is very right. The benefits of Sid's sort of design is the very reason relational databases were created in the first place. Putting 'intelligent' information into a field to be parsed by some (difficult and undefined) method is a very bad idea. Best is separate the data into separate fields (in 1 to 1 relationships) and seperate tables (1 to many and many to many releationships).

bradgrafelman

rulian wrote:
So I'm keeing my lists more flat to avoid having a million rows.

What's wrong with having a mere million rows? Other than the level of optimization it might demand upon your queries, there's nothing wrong with expecting MySQL to handle a million rows.