Link to home
Start Free TrialLog in
Avatar of Craigster2006
Craigster2006

asked on

Multiple values stored in single field with relationship to table.

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
etc


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!!


Avatar of Roonaan
Roonaan
Flag of Netherlands image
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?

-r-
Avatar of Craigster2006
Craigster2006

ASKER

Hi,
unfortunately its not possible to change the @ symbol - this is a db for another app.
Avatar of m1tk4
m1tk4
Flag of United States of America image

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

Avatar of Muhammad Wasif
Muhammad Wasif
Flag of Pakistan image
try this one

select table1.propid, table2.descr from table2
INNER JOIN table1 ON
table2.keyid IN (REPLACE( SUBSTRING(table1.Charac,2), '@', ','))
ASKER CERTIFIED SOLUTION
Avatar of limneos
limneos
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Craigster2006
Craigster2006

ASKER

Thanks Perfect man!!