SQL Query: Return fields corresponding to comma separated ..

I'm attempting a query that gathers product data for a particular
product id.

One of the items is designer(s) which can be more than one.

The product table has comma separated id's of the designers in the
designers table (which has fields like: id, fname, lname).

How can my select return the possibly several designers for a product,
with the rest of the row data, like price, name, etc.?

I'm using 4.01.

Thanks,
s7

wrote:
> I'm attempting a query that gathers product data for a particular
> product id.
>
> One of the items is designer(s) which can be more than one.
>
> The product table has comma separated id's of the designers in the
> designers table (which has fields like: id, fname, lname).

You should really have another table, which references both the designer
table and the product table. You are modelling a many-to-many
relationship, and this requires an additional table.

Using comma-separated lists to avoid the additional many-to-many table
has several disadvantages:
- Difficult to join to the designers table (as you've discovered)
- Difficult to remove an id from the list
- There's a hard limit to the number of id's that can fit in the string
- No way to automatically enforce that the id's actually reference
existing id's in the designers table
- No way to automatically enforce that the format of comma-separated
integers is followed; that is, you can enter "1,2,3,banana,6" and
nothing happens

All of these difficulties are made much easier if you use an additional
table for the many-to-many relationship, with foreign key references.

> How can my select return the possibly several designers for a product,
> with the rest of the row data, like price, name, etc.?

This might work:

SELECT p.*, d.*
FROM product AS p INNER JOIN designer AS d
ON p.designer_id_list RLIKE CONCAT('[[:<:]]', d.id, '[[:>:]]')

Then again, if the list isn't formed correctly, and the join fails in a
few cases, you'll never know if you've lost one of the designers.

Regards,
Bill K.