Multiple values stored in single field with relationship to table.

Posted on 2006-04-15
Last Modified: 2013-12-12
i have inherited a mysql database and the developers have used muliple values in a field seperated with an @ symbol.
this field contains reference numbers which relate to another table in the database.

ok so table 1 has this

propid       Charac
1             @41@869@48@874@877@878@879@881@885@870@898@901@903@
2             @41@869@48@874@877@878@879@881@885@870@898@901@903@

Table 2 has this
keyid           descr
41              desc1
42              desc2
869            desc3
48              desc4

i want to run a query so i get this

propid           Descr
1                   desc1
1                   desc3
1                   desc4
2                   desc1
2                   desc3

is this possible??
Thanks all!!

Question by:Craigster2006
    LVL 49

    Expert Comment

    It would have been for more easier when he/she would have used comma's rather than @'s. Is it possible for you to change this easily, or is that not going to work for the rest of the application?


    Author Comment

    unfortunately its not possible to change the @ symbol - this is a db for another app.
    LVL 15

    Expert Comment


    select * from table1 join table2 on table1.charac like concat('%@',table2.keyid,'@%)

    LVL 20

    Expert Comment

    by:Muhammad Wasif
    try this one

    select table1.propid, table2.descr from table2
    INNER JOIN table1 ON
    table2.keyid IN (REPLACE( SUBSTRING(table1.Charac,2), '@', ','))
    LVL 1

    Accepted Solution

    $query1="SELECT * from table1 where 1";
    while ($row=mysql_fetch_array($result1)){
    foreach ($a as $value){
    if ($a!=""){
    $query2="SELECT * from table2 where keyid='$value'";
    $result2=mysql_query($query2) or die(mysql_error());
    print "$row[propid] $row2[descr]<br>";

    Author Comment

    Thanks Perfect man!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    A colleague recently asked me about how to give his client a small part of the web site that could be completely under the client's control.  Since I have done this sort of thing before to add emergency banners to a web site, I decided I would creat…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to count occurrences of each item in an array.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now