to separate comma separated values of field and add them in integer field of temp table

Question

I have one table member_details with field "preferred_location" (varchar) that has comma separated values like "19,20,22" that come from a listbox selection ....

Now I also have another table city_master having field "city_id" (int) and "city_name" (varchar)...

Now I want to separate "preferred_location" (varchar) values and to add them in integer field of temp table so I can make an inner join between city_id(int) of the temp table and city_id(int) of city_master and then can get city name from city_name of city_master...

This is all stuff I need in MySQL - either a stored procedure or a function. I am using it with c#.net.

Answer 1

Frankly, this sounds like a bad design. If you need the integers values separately, then modify your database structure accordingly, and save the values separately to begin with.

I mean, you see where it leads to - because you stored the values as a list in a string, you have maneuvered yourself into a position where you need to unwind the values each time you want to join the tables.

That's like putting the horse behind the wagon.

Answer 2

If these integers are small, like 19,20,22 etc just use smaller 16 or 8 bit integers (as supported by your database) and it should not take much more space than a string (possibly even less).

Answer 3

Made up some mock up example, but this should work with LinqToMySql as well.

class user {
public string name {get;set;}
public int id {get;set;}
}
class member_detail {
public int user_id {get;set;}
public string prefered {get;set;}
}

class city_master{
public int code {get;set;}
public string name {get;set;}
}
void Main()
{
var users = new List<user>();
users.Add(new user(){name = "Mary",id = 1});
users.Add(new user(){name = "John",id=2});
    var details = new List<member_detail>() ;
    details.Add(new member_detail(){user_id=1,prefered="1,2,3"});
    details.Add(new member_detail(){user_id=2,prefered="3,5"});
    var cities = new List<city_master>();

    cities.Add(new city_master(){code =1,name="Moscow"});
    cities.Add(new city_master(){code =2,name="London"});
    cities.Add(new city_master(){code =3,name="Paris"});
    cities.Add(new city_master(){code =4,name="Rome"});
    cities.Add(new city_master(){code =5,name="Madrid"});

    users.Select(u=>new {u.name,cities=
    details.Where(d=>d.user_id==u.id)
    .SelectMany(d=>d.prefered.Split(','))
    .Join(cities,c=>c,d=>d.code.ToString(),(a,b)=>new {b.name})}).Dump();

}

Answer 4

thank for ur suggest ..but in my case it is better to store ids of preferred location cities in comma separated method ..

i am having one procedure that makes one temporary table and then after can make inner join with city_master table to get city names.

Create Procedure parseAndStoreList(in thingId int, in i_list varchar (128), out returnCode smallInt) BEGIN DECLARE v_loopIndex default 0; DECLARE Exit Handler for SQLEXCEPTION BEGIN call saveAndLog(thingId, 'got exception parsing list'); set returnCode = -1; END;

call dolog(concat_ws('got list:', i_list)); pase_loop: LOOP set v_loopIndex = v_loopIndex + 1; call dolog(concat_wc(',', 'at loop iteration ', v_loopIndex); LOOOP parse_loop; set returnCode = 0; END;