select multiple values for column in one query

I have a table with a column that contains a comma seperated list of
ids as TEXT that are used to reference multiple rows in another table.
so "2,4" would reference id 2 and 4 in table2.

select * from table2 where id = 3 or id = 4;

Is there a way to do that without going through the list and
delimiting with " or id = "? Something like

select * from table2 where id in values(3,4);

I thought I'd seen something like that before but I can't find it in
the mysql manual.

Is using a TEXT for an id list bad structure? Is there a better way to
store an array of ids in a column in mysql?

I guess I could do

select * from table2 order by field( id, 3, 4 ) desc limit 2;

but isn't there a way to do that in the where clause?

techcalgary@hotmail.com wrote in news:1174793194.111464.20170
@o5g2000hsb.googlegroups.com:

> I have a table with a column that contains a comma seperated list of
> ids as TEXT that are used to reference multiple rows in another table.
> so "2,4" would reference id 2 and 4 in table2.
>
> select * from table2 where id = 3 or id = 4;
>
> Is there a way to do that without going through the list and
> delimiting with " or id = "? Something like
>
> select * from table2 where id in values(3,4);
>
> I thought I'd seen something like that before but I can't find it in
> the mysql manual.
>

Without answering the main question, and trying to be helpful, can I say:

a) When you want to store an array of IDs in a table, it usually means you could
use better table design, and most often, adding a table that has a 'many-to-
many' type of relationship. In your example, I'm not quite sure what your first
selection would be, but let's say you're looking for "basketball", and you want
your query to return information about table2's id '3' and id '4'. Instead of
having 'basketball' as one column in your first table and '3,4' in the column
next to it, you'd JUST have 'basketball' in your table, with it's ID (1). Then,
in your new many-to-many table, you'd have one column referencing the first
table's ID (say, "table1ID") and a column for the tableID in table 2 (say,
"table2ID"). So, in your case, in this new many-to-many table, there are TWO
entries: i) '1' in the first column and '3' in the second and ii) '1' in the
first column and '4' in the second. Just by looking at this new table, you can
see that entry 1 in your first table('basketball') is related to ID 3 and ID 4
from the second table. A query that would return this information (untested,
example only) is:

SELECT table2.* FROM table1
JOIN newtable ON table1.ID=newtable.table1ID
JOIN table2 ON newtable.table2ID=table2.ID
WHERE table1.ID=1


> Is using a TEXT for an id list bad structure? Is there a better way to
> store an array of ids in a column in mysql?

Storing text as a primary key (which I think is your question?) is a bad idea.
Numbers are fastest, then VARCHARS i suppose, but certainly TEXT/BLOBs should be
avoided.

techcalgary@hotmail.com wrote:
> I have a table with a column that contains a comma seperated list of
> ids as TEXT that are used to reference multiple rows in another table.
> so "2,4" would reference id 2 and 4 in table2.
>
> select * from table2 where id = 3 or id = 4;
>
> Is there a way to do that without going through the list and
> delimiting with " or id = "? Something like
>
> select * from table2 where id in values(3,4);
>
> I thought I'd seen something like that before but I can't find it in
> the mysql manual.
>
> Is using a TEXT for an id list bad structure? Is there a better way to
> store an array of ids in a column in mysql?
>

Yes, this is a poor design. Google for "database normalization" for a
lot of good information.

When you have a relationship, you're generally better off creating a
third table with two columns - one for each id associated in the
relationship. Much more flexible.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================