query question

zatow

Given two tables:

table1:
t1id tinityint
name varchar

table2:
t2id tinyint
t1s varchar - comma-delimited list of t1id's

I need to write a query that returns for each t2id, a broken up list of all t1s name:
t2id(1), name(1)
t2id(1), name(2)
t2id(2), name(1)
t2id(2), name(3)

I thought the following query might do it, but it only returns the first entry:

select t2id, name from table1, table2 where t1id in (t1s)

Is there a way of getting this to work with MySQL?

Lars_Berg

Store each item separately. After that you can use an ordinary join which is much more efficient as it is possible to use indexes.

zatow

Thanks Lars, but modifying the DB is not an option. Is there any way of doing this with a single query, or do I need to write multiple queries and some code in between to parse through the comma-delimited list?

Lars_Berg

Too bad that you can't change it.

As I see you need to write multiple queries.

$q = "select  t2id,t1s from table2";
$c = mysql_query($q);
while ($r = mysql_fetch_array($c)) {
echo $r[0];
$q = "select name from table1 where t1id in (" . $r[1] . ")";
// process this query
}

tomhath

I haven't tried this, but you might be able to do it with a regular expression. Try this:

select t1s, t1id
from table1, table2
where t1s REGEX convert(varchar,t1id)

zatow

tomhath - How does this get me the name?

tomhath

You can select any of the columns in any of the tables participating in the join:

select t1id, name, t2id, t1s ...

tomhath

Just had a chance to try it and to my surprise, this works and is pretty close. But it matches too much (for example if t1s contains 11 it will match when t1id is either 1 or 11). I can't find a way to get REGEXP to use a concatenated string (I thought "(^|,)" + t1id + "(,|$)" would work, but REGEXP only looks at the first quoted string).

select t2id, t1s, name
from table1, table2
where t1s REGEXP t1id

tomhath

I was trying to use SQLServer string concatenation instead of mysql. This seems to work right:

select t2id, t1s, t1id, name 
from table1, table2 
where t1s REGEXP concat("(^|,)",  t1id, "(,|$)")