Extract value from a db column containing list of values

Question

I have a db design as

table1

id int(10);
name varchar(25);
cat_id_list varchar(255);

table2

cat_id int(10);
category varchar(50);

tab 1 - cat_id_list contains list of cat_id from tab2 I have selected some records on a particular basis from tab1. I have a specific set of cat_id 's to ignore. Now I want to delete those rec having that particular cat_id in the cat_id_list column. Can anyone help

table 1

    id      name      cat_id_list
    1       abc       1,2,
    2       ab        3,
    3       xyz       2,3,

table 2

    cat_id        category
    1             name1
    2             name2
    3             name3

I want to ignore those records from table 1 which contains '1' value in the cat_id_list.

Can you make your question more clear please? – Joao Leal Jun 27 '13 at 09:37 — Joao Leal, Jun 27 '13 at 09:37

score 2 · Answer 1 · answered Jun 27 '13 at 09:45

2

You are doing something wrong here when keeping the ids in cat_id_list. What you should be doing is to add a third table (table3) that will hold table1.id and table2.cat_id so in the sample you provided table 3 will have 4 records.

Adding a FK will help you keep your data consistent. In the FK you can define a delete cascade which will clean your data.

answered Jun 27 '13 at 09:45

asafrob

1,838
13
16

i had that option in mind... but isnt there any other soln for this...may be not at the db end...can something be done at code where i am handling these queries? – Vaishali Jun 27 '13 at 09:47
Using code you have endless options. If you use C# you can do something like: string s = "1,2,3,4,5"; string[] arr =s.Split(','); Than build your sting again and put it in the DB. – asafrob Jun 27 '13 at 09:53
i cant change or edit data in the db.. i am using php and have a func explode there... similar as split ... – Vaishali Jun 27 '13 at 09:54
i think cat_id_list shd be removed from table1 and table 3 should have id and cat_id – Vaishali Jun 27 '13 at 09:58
That sound right. Table 3 should have ID(of table 1) and cat_id of table 2. Both should be FKs and the combination should be the primary key – asafrob Jun 27 '13 at 10:05

score 1 · Answer 2 · edited May 23 '17 at 12:21

Check here. Please note that storing an array of values instead of using a separate relation (table) for relataionships many:many is not advisable (this issue illustrates why).

Edit: Actually, the link is about MySQL, but now I see that you haven't specified a DB vendor. However, the described mechanism is the same, but there might be built-in procedures for this in other DBMS (i.e. unnest(string_to_array(column, 'delimiter')) in PostgreSQL).

Edit 2: Some PHP script for this (neither the best one, nor the quickest):

$sql = 'SELECT * FROM table1';
$idsToDelete = [];
$value = 1;
while ( $row = mysqli_fetch_array ( $sql ) )
{
    if ( in_array ( $value, explode ( ',', $row [ 'cat_id_list' ] ) )
    {
        $idsToDelete[] = $row [ 'id' ];
    }
}

if ( count ( $idsToDelete ) )
{
    $sql = sprintf ( 'DELETE FROM table1 WHERE id IN ( %s )', implode ( $idsToDelete, ',' ) );
    mysqli_query ( $sql );
}

i am using php to handle the queries.. i have func explode where i can store all values from this column n save it as an array...but then after that i am not getting what to do — Vaishali, Jun 27 '13 at 09:51
See edit 2 for simple PHP code that might work. However it is OK for a few records and is a good example for something that can be made on DB and save time (especially if using separate n:n table and foreign keys) — Vasilen Donchev, Jun 27 '13 at 10:07

score 0 · Answer 3 · answered Jun 27 '13 at 10:25

0

This might inefficient but you can join those two tables on:

SELECT * From Table_1 
JOIN Table_2 ON CHARINDEX(Cast(Cat_ID AS varchar(255)) + ',',Cat_ID_List) > 0

answered Jun 27 '13 at 10:25

Joao Leal

5,533
1
13
23

Extract value from a db column containing list of values

3 Answers3