SQL Query: Return fields corresponding to comma separated ids in other table

[starman7]

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

[Bill Karwin]

st******@hotmail.com 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.

[starman7]

thanks for the clues. unfortunately, the designer ids are in one
column, separated by commas... i wonder if i could just match on the
first one, better than none...

the php code the app uses does an explode or implode to read the
designers on the product pages, but i'm not sure of the query they use.

s7

[starman7]

Bill Karwin wrote:

st******@hotmail.com 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.

bill thanks - this works great - though when there are more than one
designer, i seem to be getting the last. thanks again, mostly there is
only one designer, and better the last when there are two than none!

thanks again,
s7